topic Re: Problem with Row context in DAX Commands and Tips

Problem with Row context

pcavacas — Wed, 17 Jun 2020 18:18:16 GMT

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.

Re: Problem with Row context

lbendlin — Wed, 17 Jun 2020 20:00:56 GMT

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

Re: Problem with Row context

pcavacas — Wed, 17 Jun 2020 21:04:39 GMT

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.

Re: Problem with Row context

lbendlin — Wed, 17 Jun 2020 21:14:54 GMT

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

Re: Problem with Row context

pcavacas — Thu, 18 Jun 2020 13:07:59 GMT

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

)

Re: Problem with Row context

lbendlin — Thu, 18 Jun 2020 13:17:10 GMT

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

sumx(
var a=selectedvalue(b)

return caclulate()

)

etc.

Re: Problem with Row context

pcavacas — Thu, 18 Jun 2020 13:20:07 GMT

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

Re: Problem with Row context

lbendlin — Thu, 18 Jun 2020 13:45:39 GMT

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/

Re: Problem with Row context

pcavacas — Thu, 18 Jun 2020 13:55:35 GMT

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

)

Re: Problem with Row context

pcavacas — Thu, 18 Jun 2020 18:40:37 GMT

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

Re: Problem with Row context

lbendlin — Thu, 18 Jun 2020 19:14:22 GMT

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/

Re: Problem with Row context

pcavacas — Thu, 18 Jun 2020 20:01:13 GMT

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

Re: Problem with Row context

pcavacas — Fri, 19 Jun 2020 15:48:21 GMT

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

Re: Problem with Row context

lbendlin — Fri, 19 Jun 2020 16:25:31 GMT

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.