Open Power BI and Click on Get Data and Search for Azure Databricks and click on connect.  It will ask for below details,  Server Hostname  HTTP Path    Now we will see, how to get above details,  Go to Azure Databricks and click on Clusters  Once clusters is opened the go to Advanced setting > JDBC/ODBC. Under this we can get Server Hostname and HTTP Path, which can be used in above steps.  Fill the details and click on OK, It will ask for user credentials, after that it will open a pop up  asking to select the from List of Tables. Select Tables and click on load  In this way we can create Power BI report based on the current data received from Azure Databeicks.  In this way we can create Power BI report and create fields above it. azure BI

Hi everyone in this blog we will see the different DAX that are used to define or use the relationship between two tables. In Power BI there are two type of relationships                 1. One to One  (1:1)                 2. One to Many   (1:*) Now lets look at the DAX functions that we can use with these relationships. USERELATIONSHIP-  Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2. Syntax  USERELATIONSHIP(<columnName1>,<columnName2>) Where, columnName1         The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the many side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression. columnName2         The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the one side or lookup side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression. Key Point The function returns no value; the function only enables the indicated relationship for the duration of the calculation. Example = CALCULATE(SUM(ISales[SalesAmount]), USERELATIONSHIP(Sales[ShippingDate], DateTime[Date])) Limitations           USERELATIONSHIP can only be used in functions that take a filter as an argument. USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. RELATED – Returns a related value from another table. Syntax RELATED(<column>) Where, column        – The column that contains the values you want to retrieve. Key Point A single value that is related to the current row. Example FILTER( ‘Sales_USD’, RELATED(‘Territory'[TerritoryCountry])<>”United States”) RELATEDTABLE Evaluates a table expression in a context modified by the given filters. Where, tableName – The name of an existing table using standard DAX syntax. It cannot be an expression. Key Point A table of values. Example = SUMX( RELATEDTABLE(‘Sales_USD’)       , [Amount_USD])  Limitation The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. This function is a shortcut for CALCULATETABLE function with no logical expression. This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules. Hope this helps.

In D365 there are several option to export/Import data like Data Entities, BYOD, Aggregate measurements/Entity store. In this blog we will learn How to create entity store in D365 Finance and operations. Aggregate Measurements/Entity Stores are use to create Power Bi reports with nearly Live data where user have option to set its recurrence that is how often you want to refresh entity store there is no need to manually refresh the data as per suggested Batch job will run for respective entity stores. You can also forcefully refresh data by pressing refresh button on entity store page. So lets start with development of aggregate measurements, aggregate dimensions. Add New aggregate measurements object to the project where we required Add required attributes Add required measures Add required dimensions Add dimensions where view of dimension and aggregate measurements are different Build The Model Refresh Entity store from D365 Finance and Operation Environment Verify that respected view is created for aggregate measurement                     Add New aggregate measurements object to the project where we required.   In this step we need to right click on new item     Now select aggregate measurement and name it as per requirement in our case its “CFSAggregateMeasrure”   Now assign required views in table property of the aggregate measurement as follow     In my case I have selected “InventOnHandByWarehouse” view.                       Add required attributes Now add required attribute by right click on Attributes and assign required field in attributes as follows                         Add required measures After adding attributes add dimensions same how we added other attributes as follows   After adding measure assign required field to it and operation which you want to perform on that field (for example. :- count, Average, etc. )                           Add required dimensions By default some of the dimensions are provided like company and date which are showed in screen shot. And assign required fields in relation of dimensions                            Add dimensions where view of dimension and aggregate measurements are different If dimensions needs different view we need to create new aggregate dimension as follows   In my  case name of aggregate dimension is “CFSAggregateDimension ” Now assign required view to dimension as follows.     After this create new attributes and assign fields to that attributes as follows       After adding new dimension attribute if required you can assign more than one field reference as follows   After this step assign fields to the respective field reference as follows.     Now select the respected attribute and select its usage property and change it as key which will make it as dimensional key which will be helpful while making relations.     There are 3 options under usage property description of each as follows                               Key If you specify usage property as “key” system will define the key of the dimension using this attribute                               Parent If you specify usage property as “parent” system will parent child hierarchy with this field as parent level.                               Regular If you specify usage property as “Regular” ,this is an attribute without any special behavior and it is default value. After setting usage property attach this dimension to our aggregate measurement by dropping required aggregate measurement on dimension section of it.   Now define its dimension attribute property as follows   After this you need to make relationship among the views as follows   Fact Dimension If you want to make desired aggregate dimension as fact dimension go to desired dimension in aggregate measurement and make set “is fact dimension” property to yes/No as follows                       Build The Model   After this case build the model which is used for this development of project as follows                             Refresh Entity store from D365 Finance and Operation Environment   After successful build go to environment page’s Entity store section using following navigation System administration >> setup >> entity store and refresh the desired entity     On required entity please press the refresh button     You can also set schedule to refresh this entity by selecting edit button and enable its automatic refresh toggle and the set its recurrence as follows                             Verify that respected view is created for aggregate measurement In final step go to your VM’s SSMS and look for Axdw database in which in view section look for views with your aggregate measurement and dimension and name as follows   After select query you can see the data of that view     Now your entity store is ready to consume by power bi reporting service. Thank You!

Power BI has some new features and Future Promises for Modern Enterprise applications in Business.

Sorting can be used for defining an order direction for your Custom Visual. There are 3 different ways using which you or a user using your visual can sort your visual. They are as follows: Default Sorting: This is the easiest sorting option and gives users the ability to sort the visual by any field used in the visual. The following code needs to be added to the capabilities.json file.  “sorting”: { “default”: { } } After this the user will get the below sorting option: Implicit Sorting: Implicit Sorting can be used for pre-defining your sorting order in your capabilities.json file. Here, the user cannot manually change the sorting order. This can be done with the following code block where direction 1 is ascending and 2 is descending. Role is the data mapping name for which you would like to define your sorting. “sorting”: {         “implicit”: {             “clauses”: [                 {                     “role”: “category”,                     “direction”: 1                 }                 ]         }     } Custom Sorting: Custom sorting can be used for defining sorting in your visual.ts file and not in the capabilities.json file. Since you are defining your sorting order in your code, you can use various different logics to define your sorting(For example, you can define a formatting toggle option in the format pane that will sort the visual when turned on). A simple codeblock that can be used for sorting your datapoints in ascending order is as follows. sort((obj2, obj1) => { if (obj2.category< obj1.category)  return -1 else return 1; }); With so many options available, it is pretty easy create a visual just the way the user wants.

We can add various Visual Formatting Options while developing a Power BI Custom Visual. One of them is the Toggle feature which is useful for adding or removing particular abilities to your visual or perform any similar boolean operations. The json bit for the toggle option looks like this. By default when you add a toggle option, the option is available under a dropdown. To access the toggle option you will always have to click on the dropdown arrow. There is a neat little trick that can make you access the toggle button directly rather than clicking on the dropdown button every time. You only need to change the required toggle property name to “show”. Note: The object name for the toggle option needs to be changed to “show” in the visual.ts file as well. Your toggle option should look like this now!

Introduction: There are plenty of different custom visuals available outside of Power BI that can be used by anyone to develop some powerful visuals for free. These visuals can be downloaded from the Power BI AppSource marketplace and added in your reports. All of these visuals have also be approved by Microsoft. Steps to Download a Custom Visual: Following are the steps to download and use a Power BI Custom Visual in Power BI Desktop: Open the AppSource and select Power BI Visuals For this demonstration, I will be downloading the Dial Gauge which is a custom visual developed by CloudFronts Select on Get it now(You will have to log in with your Microsoft email id) . A pbiviz file will be downloaded. Open your Power BI Desktop and select on the ellipses in Visualizations. Click on import from file and open the pbiviz file you just downloaded. The visual should be visible with your other visuals now To get a little more familiar with the report, you can download the sample report available with the visual on AppSource. This will download a pbix report. The sample report would look something like this. I hope this blog helps you get started with Power BI Custom Visuals. Do check out our Custom Visual DialGauge as well!

Introduction: The Transport Layer Security (TLS) is a protocol that provides Secure communications. There are different versions of this protocol with the latest one being TLS 1.2. With all the crazy updates that Microsoft comes with, many of the programs, web services. etc. have enforced TLS 1.2 to be mandatory for communicating over the network. The previous versions of TLS are not supported in many of these programs and sooner or later they will deprecate for sure. Lucky for us, after the October 2018 update, Power BI Desktop now respects this need for TLS 1.2 and recognizes the Windows registry key in your System. You can enable or disable which version of TLS protocol is needed and Power BI will use that version accordingly. Steps to disable older TLS: Open your regedit by searching for ‘regedit’ in the search box of the taskbar Note: Changes in the regedit can cause serious changes in your system. Please take a backup of your regedit before proceeding and import the backup just in case your system starts to act funny. Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] and make the following changes [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] “Enabled”=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] “DisabledByDefault”=dword:00000001 This will disable your Power BI from using your older version of TLS 1.0 by default Steps to update your TLS to 1.2 Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] and make the following changes [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] “Enabled”=dword:00000001 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] “DisabledByDefault”=dword:00000000 This will enforce your applications to use the latest TLS Power BI Desktop will respect the registry keys specified on those pages, and only create connections using the right version of TLS. For further documentation on TLS, you can refer the microsoft document below https://docs.microsoft.com/en-us/windows-server/security/tls/tls-registry-settings

Introduction: In this blog we will explore some of the new features added to Power BI. Power BI has upgraded its Q&A Experience in its latest April release. It has simplified and simultaneously improved the natural language recognition process which is one of Power BI’s most powerful tools for Query Processing! Some of Q&A Explorers cool new Features: You can now add a simple image, shape or button which on being click can launch a Q&A Explorer! You just need to toggle the Q&A option on under Action for the particular image/shape/button. Adding a Q&A button can look something like this. On clicking on this newly created Q&A Explorer a dialog appears where the user can ask questions to generate dynamic visuals. To learn more about this feature you can view my previous blog on Natural Language Processing over here. You can add suggested questions which will show on the left side of the dialog when a user clicks on the Q&A button. When you click on Save and close these newly added Suggested Questions will get saved to this specific Q&A button. The Q&A Explorer can also return whole reports now when you search specific keywords. You can do this by going to a particular report and turning it’s Q&A Feature on in Page Information. Searching these keywords in the Q&A Explorer will return this particular report. Optionally, if you have page level filters then you can set Require single selection On for a particular filter. This filter will then be shown in the Dialog while searching for the queried report. Conclusion: These are some of the latest features added to Power BI’s arsenal. Q&A Explorer is an underused tool but if used correctly it can improve your interactive experience with your reports tremendously.

Introduction: Deployment of Power BI to Dynamics 365 for Finance and Operations is done by Embedded Power BI in Dynamics 365 for Finance and Operations. Configurations of Power BI in operations: Configure your LCS project within Dynamics 365 for Operations Navigate to System Administration –> System Parameters –>Go to Help Tab Here you will be asked to Connect to Life Cycle services. This operation is mandatory, it enables Dynamics 365 for Operations to established a trusted connection to LCS using your user credentials. Click on “Click here to connect to Lifecycle Services” On successful connection, you will be able to choose a set of LCS projects from the drop down menu. Select the LCS project Enable Power BI: Register Dynamics 365 for Operations deployment as an web app. 1. Login to you Power BI account 2. There are some fields we need to fill in: AppName (e.g. “D365PBI”) AppType (Server-side Web app) Redirect URI (this will be your instance URL with “oauth” at the end. E.g https://D3651611aos.cloudax.dynamics.com/oauth) Home Page URL (This will be your instance URL. E.g https://D3651611aos.cloudax.dynamics.com/) 3. Choose APIs to access 4. Then hit “Register App”. This will generate a Client ID and a Client Secret which we are going to input inside D365. 5. Keep this window open, we need to copy paste the keys into D365. Deploy Power BI Files: Navigate to System Administrator –>Deploy Power BI Files .Click on Deploy Power BI Files Here you will be asked to Authorize Power BI, Click on Authorize Power BI. Click on Deploy Power BI Files