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 

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)

 

sevenhills's avatar
sevenhills
Super User
1 year ago

Agree with your sentiments and hard time on learning with new languages. 

 

I see that you got the solution, glad to hear.

 

Tip: Do NOT try to retrofit too many measures into one. Some may say is as over engineering in the lastest technology world and also some may say as overkill. Do only for the needs! Not technical limitation. 

 

Tip: If you are pasting the query, you can click "</>", when you are typing the post or reply to a post.

Then chose C# and then paste the code and press ok and press enter. You should see the code pretty format in the post. 

 

Thank you

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