Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a report that has a bunch of columns on it and have a weird behavior that I cannot figure out what is going on. One of the formulas, the APF colum, is blank for all rows that are not at the lowest level of detail. I simplified the DAX expression to simply CountRows over a context. Below is the simple DAX expression
You can see in the image below that the APF column has a number in it on some of the rows at the lowest level, which is the correct number of rows I would expect. But if you look up the APF column the higher level groupings are all blank, which is why my more complex DAX expression is also failing. I don't understand how at the low level it has a count, but at the grouped up level it doesn't have the count.
Solved! Go to Solution.
Good to hear. What I learned from this is that only the first expression of the Calculate() is impacted by the context transition. That was not clear to me before.
I don't think this is row context. Row context applies to the raw data tables and is generally accessed via calculated columns.
Try using variables
The problem with what you are saying here, is that the actual calculation is a SumX over the APF Rates table, so I can't grab the values into variables.
SELECTEDVALUE() expects a single value in the filter context. You don't have that in a group.
I realize that SelectedValue takes a single value, but it is in a Sumx, the below DAX is closer to the real formula that I'm using. You can see that the SelectedValue is still valid because it is part of the SumX iterator.
Did you know that you can pack variables inside the sumx ?
sumx(
var a=selectedvalue(b)
return caclulate()
)
etc.
No I did not know that, but even when I change it to using variable I get the same results.
You didn't show how you changed it. What I am trying to point out is that the moment you use CALCULATE(), SELECTEDVALUE takes on a new meaning, one that you might not intend.
https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
This is the measure that I have
I created a simple PBIX example that shows my problem located here
https://oceanspray-my.sharepoint.com/:u:/p/pcavacas/ESwZvaxMNz1Kq0EjUI9m8ocBnLrTIIq1fy4egxA8yoYrOA?e...
Is the missing relationship between the tables intentional? In that case we could use TREATAS().
Please also consider this article.
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
Yes the structure of the sample is not an optimised solution, but it is a simple representation that has the same affect as my more complex full solution..
I got this to work by removing the SelectedValue in the SumX and using the fields directly.
Good to hear. What I learned from this is that only the first expression of the Calculate() is impacted by the context transition. That was not clear to me before.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |