While importing data into PowerBI we can choose the tables we want to load in the data model but we cant choose specific columns, although columns can be removed through advanced editor or by manually deleting them one by one this can also be done without using advance editor and multiple columns or tables can be deleted at once. To do this go into the data model section of your PowerBI report. Now expand the desired table and the press ctrl key on your keyboard and select the columns you want to delete. You can also select multiple tables using the same instruction. After selecting multiples columns or tables right click and select “Delete from the model” option. In the dialogue box that appears click on the “Delete” button. Then click on “Apply Changes” option which will appear in the window and you can see all those columns are now deleted Hope this helps!

Sometimes we would want to toggle some visuals in PowerBI on the basis of a button click for instance when clicking a button a certain visual shows up and while clicking the same or another button the visual hides itself. This can be easily done with the help of bookmarks in PowerBI. In this blog we will hide and show the slicer panel. First let’s insert a right arrow button from the Buttons option in the insert tab. And place this newly created button in the extreme left of our report canvas. Now click on View tab from the Ribbon and enable Bookmarks Pane and Selection Pane. Click on Add option in Bookmarks and create a new bookmark named Slicer Invisble Now again go to Insert tab and from shapes draw a Rectangle over the button. Now on the rectangle add a slicer and populate it with desired values also add a left button so as to close the slicer pane. Now add another bookmark named Slicer Visible in the Bookmarks pane. Now in the selection pane hide the first button we had created and update the Slicer Visible Bookmark. Similarly keep the first button visible and keep the shape, slicer, and second button hidden. Update the Slicer Invisible bookmark accordingly. Right click on both the bookmarks and uncheck the Data option so as to not record changes in data. Select the second button and toggle the Action button as on from type select bookmark and from bookmark select slicer Invisible. Similarly click on the first button and set its action to slicer visible bookmark The toggle buttons will open and close the slicer pane as required. Thank you for reading my blog hope it helped.

Sometimes we would want to ask users about their thoughts , opinions or ask some questions along with a PowerBI report. That is, we would need  to collect information about the user through PowerBI App.  This can be done by creating a Microsoft Form inside PowerBI interface to collect user information. This can be demonstrated as: Go to forms.office.com and create a new form. Insert the Title , Description and add some fields in my case I have created a Test form with a field for giving ratings. Click on share and copy the link. Go to app.powerbi.com , create a new workspace and while creating enable develop a template app option Add your existing PowerBI reports here by clicking “New” option and then click on create app. You can add Name, description and logo to this app also you can choose a theme Click on Navigation tab and enable new navigation builder Click on the  New button on the navigation pane and select link. Enter the Name of the form and enter Microsoft form link Publish the app then select go to app As you can see the form is embedded in PowerBI interface The responses done by users gets saved at forms.office.com This can be used in a scenario where we need feedback for a  PowerBI report Hope this blog helped Note: Not all Power BI users can view and interact with apps. If you have a free license, you can open apps that are stored in Premium capacity and have then been shared with you. If you have a Pro license, you can open apps that have been shared with you.

Introduction: Sometimes while refreshing our dataset in PowerBI or importing new data from existing sources we may encounter OLE DB or ODBC errors as shown in the image below. This might be due to caching issues. To solve this follow the steps: Go to File tab on the ribbon in PowerBI Desktop , then click on About and check the PowerBI version, make sure it is the latest version if not update PowerBI. If PowerBI version is latest, click on the dropdown arrow below the Transform Data button. Click on Data source settings Click on Global Permissions Select the Data Source and click on Clear Permissions and click on close. Click on close and again select the data source as new and enter the credentials. This time the dataset would be loaded without errors. Hope this article helped

Part 4: Testing using Developer Portal and JWT Policy Configuration Introduction Configuring OAuth 2.0 for your APIs hosted in Azure API Management adds an extra layer of security and prevents unauthorized access. This is a very important configuration form Security point of view for your Endpoints and is provided out of the box by Azure. This is the second part of a series of Blogs on Securing your API using OAuth 2.0 in Azure API Management. Please go through all the parts to find easy and detailed steps that will help you configure the OAuth 2.0 Authentication. Successfully call the API from the developer portal Note: This section is new to the Developer Portal and is under changes. So sometimes it might give Un-Authorized or CORS error. Hopefully, this bug gets resolved in future soon. Now that the OAuth 2.0 user authorization is enabled on your API, the Developer Console will obtain an access token on behalf of the user, before calling the API. Browse to any operation under the API in the developer portal and select Try it. This brings you to the Developer Console. Note a new item in the Authorization section, corresponding to the authorization server you just added. Select Authorization code from the authorization drop-down list, and you are prompted to sign in to the Azure AD tenant. If you are already signed in with the account, you might not be prompted. After successful sign-in, an Authorization header is added to the request, with an access token from Azure AD. Sign in to the portal Click on accept Note an Authorization header is added to the request The following is a sample token (Base64 encoded): Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6ImppYk5ia0ZTU2JteFBZck45Q0ZxUms0SzRndyJ9.eyJhdWQiOiJkMTYyODJhYy05N2RlLTRlNGYtODVkZi0xNmVlZDUwNjNjNWUiLCJpc3MiOiJodHRwczovL2xvZ2luLm1pY3Jvc29mdG9ubGluZS5jb20vMjZjNGIyZTQtZWMwNy00YzdiLTkyZTUtOTdmNTI4NjVlOThiL3YyLjAiLCJpYXQiOjE2MDEyOTIxODYsIm5iZiI6MTYwMTI5MjE4NiwiZXhwIjoxNjAxMjk2MDg2LCJhaW8iOiJBVlFBcS84UkFBQUE4RmJoME4rOHFhVVpZSVRDY1hBVXVNOXZMOVNleUhHWnJnSUtiOUJkNW9HWEpBS1ArMEl5Q1FmUGx2NWo0amIxL0tKa0dLeHFOeWRDZlk3cTN3NGMzbHZsK3ovSFl3VmljNHJKMTBPakFsYz0iLCJhenAiOiIzYzcyZjU3ZC04M2YxLTQxMzktOTIzMi05YWFlODNjZTY2NjQiLCJhenBhY3IiOiIxIiwibmFtZSI6IlJ1c2hhbmsgS2FyZWthciIsIm9pZCI6IjU3Mzc4NTNkLTVhZTEtNDU4Ni05YjUzLTE5OWI0NDcyYWVkNyIsInByZWZlcnJlZF91c2VybmFtZSI6InJrYXJla2FyQGNsb3VkZnJvbnRzLmNvbSIsInJoIjoiMC5BQUFBNUxMRUpnZnNlMHlTNVpmMUtHWHBpMzMxY2p6eGd6bEJrakthcm9QT1ptUlVBQTQuIiwic2NwIjoiRmlsZXMuUmVhZCIsInN1YiI6IjY5UGhwWTFKNm1qS3ZhT1FaeDdaNm1jM2hpV2RVUXFoVTZpczZPdVdmZXMiLCJ0aWQiOiIyNmM0YjJlNC1lYzA3LTRjN2ItOTJlNS05N2Y1Mjg2NWU5OGIiLCJ1dGkiOiI5UTB0S1hBWkFreWpMdGw5TE9nNUFBIiwidmVyIjoiMi4wIn0.S-Wx7iH8TK4aW2Wi2msP6wu__oAmUdvpFAaAZi3_n_by2C7ElJRSeBvjYwVImsEzw4gg1zGm6ssH0xUcj2YJ3tZ5ddFW8IZR1DICHvT_sUXFCrRg6ZDlj_VPzeyLor_RmabgSE4ZfMQOmrfCET51AnQHS96-lIp_cB6SkddWQielfQMebhMMVPNyjLsBcSmFxY4gk0e3cEWnGPQHQRRMxwnJnJeqv8Gfm4fMD_xwD05nGyQ3M_mZt3H0UZiLjvVwsRlS9t7MPhVJCZPZBxEIkg0U_2IOE9OQEmuKwdyBLjVM8dNFUxfEOFUvoYUvJ-hd8hwxB5CXBYdQG9kLiAJpOg Select Send, and you can call the API successfully. Configure a JWT validation policy to pre-authorize requests At this point, when a user tries to make a call from the Developer Console, the user is prompted to sign in. The Developer Console obtains an access token on behalf of the user and includes the token in the request made to the API. 1. However, what if someone calls your API without a token or with an invalid token? For example, try to call the API without the Authorization header, the call will still go through. The reason is that API Management does not validate the access token at this point. It simply passes the Authorization header to the back-end API. You can use the Validate JWT policy to pre-authorize requests in API Management, by validating the access tokens of each incoming request. If a request does not have a valid token, API Management blocks it. For example, add the following policy to the <inbound> policy section of the Echo API. It checks the audience claim in an access token, and returns an error message if the token is not valid. For information on how to configure policies, see Set or edit policies. <validate-jwt header-name=”Authorization” failed-validation-httpcode=”401″ failed-validation-error-message=”Unauthorized. Access token is missing or invalid.”>     <openid-config url=”https://login.microsoftonline.com/{aad-tenant}/.well-known/openid-configuration” />     <required-claims>         <claim name=”aud”>             <value>{Application ID of backend-app}</value>         </claim>     </required-claims> </validate-jwt>  Note This openid-config URL corresponds to the v1 endpoint. For the v2 openid-config endpoint, use https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration. For our scenario the XML Policy is as follows: <validate-jwt header-name=”Authorization” failed-validation-httpcode=”401″ failed-validation-error-message=”Unauthorized. Access token is missing or invalid.”>     <openid-config url=”https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration” />     <required-claims>         <claim name=”aud”>             <value>d16282ac-97de-4e4f-85df-16eed5063c5e</value>         </claim>     </required-claims> </validate-jwt> Now go back to the developer console and without selecting the Authorization Code try to click on send. The Error Message is displayed. Reference Link: https://docs.microsoft.com/en-us/azure/api-management/api-management-howto-protect-backend-with-aad Part 1: Configuration of Applications in Azure AD  Part 2: Configuration of an Application (Client App) in Azure AD for Consumer  Part 3: OAUTH 2.0 Server setup  Part 4: Testing using Developer Portal and JWT Policy Configuration

Part 2: Configuration of an Application (Client App) in Azure AD for Consumer Introduction Configuring OAuth 2.0 for your APIs hosted in Azure API Management adds an extra layer of security and prevents unauthorized access. This is a very important configuration form Security point of view for your Endpoints and is provided out of the box by Azure. This is the second part of a series of Blogs on Securing your API using OAuth 2.0 in Azure API Management. Please go through all the parts to find easy and detailed steps that will help you configure the OAuth 2.0 Authentication. Grant permissions in Azure AD Now that you have registered two applications to represent the API and the Developer Console, you need to grant permissions to allow the client-app to call the backend-app. Go to the Azure portal to grant permissions to your client application. Search for and select APP registrations. Choose your client app. Then in the list of pages for the app, select API permissions. Select Add a Permission. Under Select an API, select My APIs, and then find and select your backend-app. Under Delegated Permissions, select the appropriate permissions to your backend-app, then select Add permissions. Optionally, on the API permissions page, select Grant admin consent for <your-tenant-name> to grant consent on behalf of all users in this directory. Part 1: Configuration of Applications in Azure AD  Part 2: Configuration of an Application (Client App) in Azure AD for Consumer  Part 3: OAUTH 2.0 Server setup  Part 4: Testing using Developer Portal and JWT Policy Configuration

Part 1: Configuration of Applications in Azure AD Introduction Configuring OAuth 2.0 for your APIs hosted in Azure API Management adds an extra layer of security and prevents unauthorized access. This is a very important configuration form Security point of view for your Endpoints and is provided out of the box by Azure. This is the first part of a series of Blogs on Securing your API using OAuth 2.0 in Azure API Management. Please go through all the parts to find easy and detailed steps that will help you configure the OAuth 2.0 Authentication.Open Xrm Toolbox and add Tool “PowerBI option-Set Assistant” Register an application (Backend App) in Azure AD to represent the API To protect an API with Azure AD, the first step is to register an application in Azure AD that represents the API. Go to the Azure portal to register your application. Search for and select APP registrations. Select New registration. When the Register an application page appears, enter your application’s registration information: In the Name section, enter a meaningful application name that will be displayed to users of the app, such as backend-app. In the Supported account types section, select an option that suits your scenario Leave the Redirect URI section empty. Select Register to create the application. On the app Overview page, find the Application (client) ID value and record it for later. Select Expose an API and set the Application ID URI with the default value. Record this value for later. Select the Add a scope button to display the Add a scope page. Then create a new scope that’s supported by the API (for example, Files.Read). Finally, select the Add scope button to create the scope. Repeat this step to add all scopes supported by your API. When the scopes are created, make a note of them for use in a subsequent step. Register Consumer application in Azure AD to represent a client application (Client App) Every client application that calls the API needs to be registered as an application in Azure AD as well. In this example, the client application is the Developer Console in the API Management developer portal. Here’s how to register another application in Azure AD to represent the Developer Console. Go to the Azure portal to register your application. Search for and select APP registrations. Select New registration. When the Register an application page appears, enter your application’s registration information: In the Name section, enter a meaningful application name that will be displayed to users of the app, such as client-app. In the Supported account types section, select option as required. In the Redirect URI section, select Web and leave the URL field empty for now. Select Register to create the application. On the app Overview page, find the Application (client) ID value and record it for later. Now, create a client secret for this application to use in a subsequent step. From the list of pages for your client app, select Certificates & secrets, and select New client secret. Under Add a client secret, provide a Description. Choose when the key should expire, and select Add. When the secret is created, note the key value for use in a subsequent step. Part 1: Configuration of Applications in Azure AD  Part 2: Configuration of an Application (Client App) in Azure AD for Consumer  Part 3: OAUTH 2.0 Server setup  Part 4: Testing using Developer Portal and JWT Policy Configuration

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!

In this blog we will see how you can prevent yourself from coming into the defaulter list. Lets first understand the different criteria for a user to fall in defaulter list. The follow up date of the case is less than today’s date. The day difference between last notes added and follow up date is greater than 5 days. For critical priority case notes not added after every two hours. Now since we know the criteria we can make a habit of always looking at our assigned cases either when we logged in or while logging out so that our follow up dates will not miss. As notes on the case are very important not just for the defaulter list but for clients and management to get insights of what is happening on the case, we should put notes on every action we do on the case. Hope this helps.