Forum Discussion

Steve_Scotland's avatar
Steve_Scotland
Helper I
1 year ago
Solved

Using SWITCH or IF for parameters required for the table in DAX Filter

I have the following two examples of Calculations using a FilteredDates VAR in my Measures for a FILTER The only difference is what I am passing to the filter ie for Week TY I am passing ALL...
Need Help
  • Steve_Scotland's avatar
    Steve_Scotland
    1 year ago

    sevenhills 

    It turns out the solution was to put the SWITCH in to the Filter as per the below

    Fluctuating between VERY pleased  &  annoyed.
    Will have to do reams of testing.   I'm worried it comes under the header of "Shouldn't work but does" - ha ha.  

    For this example, I've changed the VAR  Calcs_Needed      to      SelectedTimeFrame
    Apologies for the rubbish formatting in the copy/paste

    // ***************************************************
    // // The Calc bit :

    VAR FilterDates =

    FILTER( ALL(Dates), SWITCH( TRUE,

    SelectedTimeFrame = "Week TY",
    Dates[Week] = Reporting_Week_TY &&  Dates[Year] = Reporting_Year_TY,

    SelectedTimeFrame = "Period TY",
    Dates[Week] <= Reporting_Week_TY &&  Dates[Period] = Reporting_Period && Dates[Year] = Reporting_Year_TY,

    SelectedTimeFrame = "YTD TY",
    Dates[Week] <= Reporting_Week_TY && Dates[Year] = Reporting_Year_TY )
    )

    VAR Hold_Calc =
    SUMX ( FilterDates, [Sales Value] )

    RETURN
    IF(ISBLANK(Hold_Calc), 0, Hold_Calc)

     

Steve_Scotland's avatar
Steve_Scotland
Helper I
1 year ago

sevenhills 

Apologies.  Divided by a common language

When I said retrofit  I meant  I have a measure for  TY  with VAR etc defined that says

Sales Week TY =
VAR Hold_Calc = SUMX( FILTER(ALL(Dates),                  
            Dates[Week]= Reporting_Week_TY && CR_Ret_Day_Dates[Year] = Reporting_Year_TY),
           
[Sales])

Return     IF ( Hold_Calc = BLANK(),0, Hold_Calc)


~~~~~~~~

and a COMPLETELY DIFFERENT meaure

Sales Week LY =
VAR Hold_Calc = SUMX( FILTER(ALL(Dates),                  
            Dates[Week]= Reporting_Week_LY && CR_Ret_Day_Dates[Year] = Reporting_Year_LY),
           
[Sales])

Return     IF ( Hold_Calc = BLANK(),0, Hold_Calc)


~~~~~~~~~

where the only diffirence is one uses FILTERDATES for


Reporting_Week_TY && CR_Ret_Day_Dates[Year] = Reporting_Year_TY


                          and the other

Reporting_Week_LY && CR_Ret_Day_Dates[Year] = Reporting_Year_LY

NOW what I can do is define whether it "WEEK TY"  or "WEEK LY"  at the top of each **individual measure**, not mergeing them,   but use the same code in the HokldCalc  ie (curtailed version)

VAR FilteredDates =

FILTER(
ALL(CR_Ret_Day_Dates),
SWITCH( TRUE,

TimeFrame = "Week TY",
CR_Ret_Day_Dates[Week_Int] = Reporting_Week_TY &&
CR_Ret_Day_Dates[Year Int] = Reporting_Year_TY,

TimeFrame = "Week LY",
CR_Ret_Day_Dates[Week_Int] = Reporting_Week_LY &&
CR_Ret_Day_Dates[Year Int] = Reporting_Year_LY
)
)


VAR HoldCalc =
SUMX(FilteredDates, [Sales])


Return     IF ( Hold_Calc = BLANK(),0, Hold_Calc)

RESULT !  

sevenhills's avatar
sevenhills
Super User
1 year ago

Thanks for you explanation! Glad to hear it is all working out.