Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pcavacas
Helper I
Helper I

Problem with Row context

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

APF =
COUNTROWS(FILTER (
ALL ( 'VF Demand Forecast' ),
'VF Demand Forecast'[PlanAccountID] = SELECTEDVALUE ( 'VF APF Rate'[PlanAccountID] )
&& 'VF Demand Forecast'[PrdId] = SELECTEDVALUE ( 'VF APF Rate'[Product Code] )
&& 'VF Demand Forecast'[Date] >= SELECTEDVALUE( 'VF APF Rate'[MinDate] )
&& 'VF Demand Forecast'[Date] <= SELECTEDVALUE( 'VF APF Rate'[MaxDate] )
)

 

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.

 

Annotation 2020-06-17 141322.png

1 ACCEPTED 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.

View solution in original post

13 REPLIES 13
lbendlin
Super User
Super User

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

 

APF =
var v1 = SELECTEDVALUE ( 'VF APF Rate'[PlanAccountID] )
var v2 = SELECTEDVALUE ( 'VF APF Rate'[Product Code] )
var v3 = SELECTEDVALUE( 'VF APF Rate'[MinDate] )
var v4 = SELECTEDVALUE( 'VF APF Rate'[MaxDate] )
return COUNTROWS(FILTER (
ALL ( 'VF Demand Forecast' ),
'VF Demand Forecast'[PlanAccountID] = v1
&& 'VF Demand Forecast'[PrdId] = v2
&& 'VF Demand Forecast'[Date] >= v3
&& 'VF Demand Forecast'[Date] <= v4
)
 
That way you preserve the important parts of the filter context before you destroy it with ALL().

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.

 

SUMX (
'VF APF Rate',
'VF APF Rate'[Rate]
* CALCULATE (
[Latest Estimate Revenue],
FILTER (
ALL ( 'VF Demand Forecast' ),
'VF Demand Forecast'[PlanAccountID] = SELECTEDVALUE ( 'VF APF Rate'[PlanAccountID] )
&& 'VF Demand Forecast'[PrdId] = SELECTEDVALUE ( 'VF APF Rate'[Product Code] )
&& 'VF Demand Forecast'[Date] >= SELECTEDVALUE( 'VF APF Rate'[MinDate] )
&& 'VF Demand Forecast'[Date] <= SELECTEDVALUE( 'VF APF Rate'[MaxDate] )
)
)
)

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

SUMX (
'VF APF Rate',
var planAccountId = SELECTEDVALUE ( 'VF APF Rate'[PlanAccountID] )
var productCode = SELECTEDVALUE ( 'VF APF Rate'[Product Code] )
var minDate = SELECTEDVALUE( 'VF APF Rate'[MinDate] )
var maxDate = SELECTEDVALUE( 'VF APF Rate'[MaxDate] )

Return
'VF APF Rate'[Rate]
* CALCULATE (
[Latest Estimate Revenue],
 
FILTER (
ALL ( 'VF Demand Forecast' ),
'VF Demand Forecast'[PlanAccountID] = planAccountId
&& 'VF Demand Forecast'[PrdId] = productCode
&& 'VF Demand Forecast'[Date] >= minDate
&& 'VF Demand Forecast'[Date] <= maxDate
)
 
)
)

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.