Problem with Row context

[pcavacas]

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.

[lbendlin]

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().

[pcavacas]

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.

[lbendlin]

SELECTEDVALUE() expects a single value in the filter context. You don't have that in a group.

[pcavacas]

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] )

)

)

)

[lbendlin]

Did you know that  you can pack variables inside the sumx ?

sumx(
var a=selectedvalue(b)

return caclulate()

)

etc.

[pcavacas]

No I did not know that, but even when I change it to using variable I get the same results.

[lbendlin]

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/

[pcavacas]

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

)

)

)

[pcavacas]

I created a simple PBIX example that shows my problem located here
https://oceanspray-my.sharepoint.com/:u:/p/pcavacas/ESwZvaxMNz1Kq0EjUI9m8ocBnLrTIIq1fy4egxA8yoYrOA?e...

[lbendlin]

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/

[pcavacas]

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

[pcavacas]

I got this to work by removing the SelectedValue in the SumX and using the fields directly.

[lbendlin · 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.