Hi in this blog we will see how we can add rows to power bi dataset for a given date range. For example if you have a dataset which has start and end date and you want rows for each date between this range then this blog will help you. Step 1: Open Power BI load your dataset and go to transform data. Right click on your from date column and change its type to date. Step 2: From add columns click on custom column Step 3: In custom column formula add following code: { Number.From([From])..Number.From([To]) } Step 4: Expand this column to new rows to get your result. Step 5: Change the datatype of this column to date. In this way you can prepare your dataset for all the dates present in your From and To column. Hope this helps.

Consider a scenario where a start and end date along with the total duration for a particular task has been given to us. We have to distribute the total duration equally among all dates between the start and end date. We can solve this issue by the combination of Power Query and DAX. Lets see the steps First we need to generate the list of dates from start date till end date. Open Advance Editor As you can see the table I’m working on have two columns for start and end date for a particular task respectively. In Power Query we cannot generate a list between dates so first we have to convert the data type from date to numeric. This can be easily done by right clicking the desired column and changing the data type right away. After the data type of respective columns is changed into numeric, click on the “Add Column” option in the ribbon and select custom column. To generate a list in Power Query the syntax is “starting number .. Ending number” so we apply this syntax in accordance to our needs. The “Number” function is make to sure to take only numeric values to avoid any conflicts. After validating the code press the “OK” button. You can see a new column with lists. Click on the Expand button on the top right of the column. After Expanding the column you’ll see a list of numbers. Since these numbers are numeric we have to again convert them in Date format. This can be done by right clicking on the column and changing its Data Type. As you can see we can see all the dates between start and end dates now. 2. Since we generated the list of dates. We Proceed to distribute duration equally Create a new Calculated Column in PowerBI Desktop Write the following DAX. actual hours = Sheet1[original estimate]/ CALCULATE(count(Sheet1[taskid]),FILTER(Sheet1,Sheet1[taskid]=EARLIER(Sheet1[taskid]))) This code divides the Duration assigned for a task by the count of the total tasks where the task id is same. As you can see the Original Estimate column which is for total duration for a task is equally divided into a new column called “actual hours”. You cant see dates of Saturday, Sunday as I filtered these dates in Advanced Editor itself as they are non working days. This can be modified according to requirement. Thank you for reading hope this article helped

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.