Power BI DAX – Create a percentage across days of week (ALLEXCEPT. ALL. DIVIDE)

We just looked at the following request

We have sold specific quantities of products. Can we look at a week for a year in a slicer and see the percentage of items sold for each day. Which days do we sell the most products overall?

We have the following model (Just the parts that will be required for this exercise

And thanks to the Power BI Forums for giving me some help on figuring this out.

What we want to end up with is:

  • A year Slicer
  • Product category on rows of a matrix
  • The Day Name from Date on the Columns of the Matrix
  • And % Quantity for that day based on all the days of the week as the value

Power Query Editor

At first I believed that I needed to add some kind of Ranking order for the DAX to use and as this will be a column its better to do in Power Query Editor (Or in the Sort DB)

To speed things up I created a Year Week Column in SQL DB consisting of the following examples

  • 202101
  • 202102
  • 202135

So the weeks 1 to 9 was padded out with a zero. I then formatted this to a number field and called it Year Week. Every seven rows within that week in the date table will have the same Year Week Number Applied

Measure

I can now create the measure. Lets have a look at it in a bit more detail

Order Quantity by Week % = Var varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), FILTER(ALL(DimDate), DimDate[Year Week]))
RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek)

And this measure is then set as a percentage.

First of all we create a variable. It gets the Sum of Order Quantity and it filters by using an ALL on the column Year Week we just created in the Date Table

“ALL Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied (On Year Week).”

And we return the sum of order Quantity (Which in the context is just for, for example Friday and Accessories by the Sum of Order Quantity

This appears to work

We have Implicitly filtered by Product Category and Day Name of Week.

the question here is, did we even need to set ALL on Year Week column in date. Could we have just said use the entire date

Order Quantity by Week % = Var 
varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), ALL(DimDate)) 

RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek)

This is working in the same way and makes sense to me. We are using all the dates in the date table. And removing the Filter will create a faster DAX query.

Its looking at the entire data set and we can see that everything totals to 100%. So for us, taking into account all years Thursdays look like a good day especially for accessories.

However we don’t want to analyse the full data set and when we add in a year slicer the logic fails

As you can see, the story is still true but its telling the wrong story. Now for Accessories we have 31% of sales happening in 2021, attributing to smaller percentages across the days.

So we want to change the DAX to accept the Year Slicer

Order Quantity by Week % = 

Var VarALLEXCEPT = CALCULATE(SUM(FactResellerSales[Order Quantity]), ALLEXCEPT(DimDate,DimDate[CalendarYear]))

RETURN 
DIVIDE(SUM(FactResellerSales[Order Quantity]), VarALLEXCEPT)

And this appears to now work just taking the year 2012 into consideration because we are using ALLEXCEPT

Thursday is definitely a good day.

“ELLEXCEPT Removes all context filters in the table except filters that have been applied to the specified columns.”

So we create a variable to get the Sum or Order Quantity with ALLEXCEPT our Date Slicer which in this case is the calendar year slicer

So we are using 2012 from the date Dimension in the slicer. And Bikes from Product and Thursday as the Week Name from date in the Matrix

We Divide the Sum of Quantity which has all the filters applied. Bikes. Thursday. 2021

By the Sum Of Quantity with all, Except we do apply the Calendar Year.

  • DIVIDE(50/100)
  • 50 is Bikes on a Thursday in 2012
  • 100 is Bikes in 2012

DAX is something I do struggle with a little. You think you understand it. Turn your head from the screen and its gone again.

But hopefully this goes a little way to understanding how this DAX in this specific context has been approached

Power BI AI Visual Smart Narratives

Power BI has given us the following AI visuals:

  • Key influencers
  • Q&A Visualisation
  • Decomposition Tree

And now we also have smart narratives to work with. Many companies want to create report that includes text in regards to the report they are looking at to help the user understand the key takeaways. This usually involves someone manually adding in text to the reports.

Not great for interactive reports that update on a regular basis. this is where smart narratives come in. Smart narratives create the text in an interactive way for you.

If its good, it should eliminate the need to add in information manually for your users, which is a huge win.

Lets have a look at it in action on the Adventureworks Report

At the most basic level you can simply drag smart narratives into your report

Smart narratives describes your data to you. This is all really good information. what happens when I select a data point on the report like select 2011 in Sales amount and order quantity

The narrative updates to tell you about just 2011 so our smart narrative is even better, It updates with every click the user makes.

For my Top 10 Products table, I want a smart narrative that just gives me information on this visual and not every visual on the page so I right click on the visual and choose Summarize

And again, we are getting good information for that specific visual. You need to think whether the visual can tell this story on its own or if narrative would help. I tend to see smart narratives as something that sits on a page to tell a story across all the visuals.

Changing the Summary

This is great but there will be times when you do want specific information in your smart narrative. I am going to remove the smart narrative for the visual, and replace with one for the entire products report page.

Click on the smart narrative and then +Value to add a value

I want it to tell me how many items have been sold of top value products. the red line tells me that it doesn’t know what sold means and we need to define this item .

Click on Sold and then Define Sold

Here I go to Field Synonyms in the Q&A Setup to define a new synonym for order quantity because order quantity can be expressed as quantity sold. I have added quantity sold as this is what has been searched for.

Back in Teach Q&A I ask the question again

And this time it understands what I mean. I submit this to train the Q&A. and this time we find this specific query because its in Q&A. I can now format the question to get an answer for the smart narrative

After saving, the number simply appears in the smart narrative. I deleted it and moved the cursor to the beginning on the smart narrative. then I wrote.

The quantity sold of top value products is:

And then clicked review, found the item and + to insert item to place it exactly where I want it

from this I can see that I can use the Q&A tool to format questions that I can then add into the smart narrative, adding synonyms where necessary to create even more easy to understand questions.

Smart narratives and Q&A work perfectly together.

Power BI October 21 Updates GetUserArtifactAccessAsAdmin API through to PowerShell

There have been some new API updates (September October Power BI 2021) and I thought I would see what they were all about and how you can use them starting with the API mentioned in the October updates.

I haven’t used these APIs before and I thought it would be interesting to see how the documentation holds up.

So the first start at https://powerbi.microsoft.com/en-us/blog/power-bi-october-2021-feature-summary/#post-17563-_Toc84518701

And this links you you to https://docs.microsoft.com/en-gb/rest/api/power-bi/admin/users-get-user-artifact-access-as-admin

So this API returns all the artifacts for a given user which I imagine would be really useful when someone leaves the company and you need to assess the objects that they may own.

To use the API you need to be Power BI admin or Global Admin. I have the Power BI admin so I am good to go.

Delegated permissions are supported. I imagine this needs a lot more investigation as to why and how you would set this up.

There are some limitations, For example you can only make 200 requests per hour. You can click on Try it to try out the API from this URL by logging into your account

But this API’s need you to get the userGraphId. my first question is, What is a userGraphID and how can I get it?

It is mentioned in the documentation. ‘The graph ID of user’

And to be honest that is unhelpful. I have no idea how to get this information to run the API. Its just assumed. I have also asked on the forums and no one else seems to know either.

So to just get an idea of how you would use these APIs, I’m working with one that doesn’t need the userGraphIDhttps://docs.microsoft.com/en-us/rest/api/power-bi/admin/groups-get-groups-as-admin#code-try-0

This will get me out all the objects in power BI and for the time being I just want the top 10.

I added the $expand name and value by creating the name and value and hitting the +

Then hit Run to make sure it works

So where do we use it? Open up Windows Powershell

We can run the commands via Windows Powershell. https://docs.microsoft.com/en-us/powershell/scripting/overview?view=powershell-7.1 ISE (Integration Scripting Environment)

powershell_ise.exe

And type in

 login-PowerBI

And now you can create your code

I simply added the URL from the Try it above (Without the Authorisation Line)

Value[0] Simply means give me the first element inside this command and run.

I can go even further an parameterise the URL by doing the following

Param(
[string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=10&%24expand=users%2Creports%2Cdashboards%2Cdatasets'
)
login-PowerBI
$result = Invoke-PowerBIRestMethod -Url $url -Method Get 
$workspaceContents = $result | convertFrom-Json
$firstworkspace = $workspacecontents.value[1]
$firstworkspace

This is great

  • But what if I want to automate this and, for example create a json file from it once a month?
  • And what if I don’t want to have to run it as the Power BI Admin?

Back to the original question regarding userGraphId

It seems that many other people have asked this question. I got a possible answer on the Power BI Issues forum saying that the userGraphID is the users Object ID that you can find in azure Active Directory – Users.

So I went into Azure – Azure Active Directory – Users and took my Object ID

I added the Object ID and got the results back that I was hoping for.

Great. This gives all the artifacts that I own. Can I now do this in Powershell like the previous API? Open Powershell again.

powershell_ise.exe

Can I reuse the Previous Script.

Open my previous Script. InvokePowerBIRESTMethos.ps1

 Param(
    #commented out the other API
    #[string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/groups?           %24top=10&%24expand=users%2Creports%2Cdashboards%2Cdatasets' 

    [string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/users/objectID/artifactAccess'

   )

login-PowerBI


$result = Invoke-PowerBIRestMethod -Url $url -Method Get 

$workspaceContents = $result | convertFrom-Json

$firstworkspace = $workspacecontents.value[1]
$firstworkspace 

And this works. I get back all my objects

So this is just the initial investigation. To do

  • How to Delegate Permissions for this API?
  • How to Automate the Powershell script and create a dataset to work with?

And even further research

  • can we automate checking between a list of users from Yesterday and today to see who has gone to automate the process even further?
  • Is it possible that Azure can handle use cases where we know the user is leaving and have assigned a leave date in Azure Active Directory?
  • Then Feed this loop into the API to generate this list so we know what reports need to have their ownership taken over
  • Automate the handling of take over accounts

Create your website with WordPress.com
Get started