Forum Discussion

pcavacas's avatar
pcavacas
Helper I
6 years ago
Solved

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 l...
  • lbendlin's avatar
    lbendlin
    6 years ago

    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.

lbendlin's avatar
lbendlin
Super User
6 years ago

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's avatar
    pcavacas
    Helper I
    6 years ago

    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's avatar
      lbendlin
      Super User
      6 years ago

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

      • pcavacas's avatar
        pcavacas
        Helper I
        6 years ago

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