Help with a DAX Switch (Filter with a Related Table)

DDL1976 — Tue, 11 May 2021 15:21:41 GMT

Hi there,

I have a DAX function that is working, but I want to extend the logic so its Dynamic rather than hard coded..

I have a slicer that selects 1 of the 2 options in the table below 'Report up to'

Then a little piece of DAX to feed the Switch Function.

Select Report To = MIN('DimReport To'[Switch])

Here is the DAX containing the SWITCH logic

Report To = SWITCH([Select Report To], 1, Calculate (sum(FactActualAndPlans[Value]), Filter(FactActualAndPlans, RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30))) , 2, SUM(FactActualAndPlans[Value])

In the first option of the switch I need the date of 2020,04,30 to reference the date in the first row of table below called ('DimReport To') that can periodically change. (This date could be placed in a different table if that makes it more simple?)

Period End Date Switch Type 28 February 2021 1 Finalised Periods Only 31 December 2999 2 All Periods

I hope I have explained it clearly enough...

Thanks in advance for the help of the community!!!

Re: Help with a DAX Switch (Filter with a Related Table)

amitchandak — Wed, 12 May 2021 03:58:16 GMT

DDL1976 , Something like this

SWITCH(True()
[Select Report To] =1 && RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30) , Calculate (sum(FactActualAndPlans[Value]), Filter(FactActualAndPlans, RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30))) ,
[Select Report To] = 2, SUM(FactActualAndPlans[Value])
)

topic Help with a DAX Switch (Filter with a Related Table) in DAX Commands and Tips

Help with a DAX Switch (Filter with a Related Table)

Re: Help with a DAX Switch (Filter with a Related Table)