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)

     

sevenhills's avatar
sevenhills
Super User
1 year ago

I think this is already implemented by many. Please see these links:
https://www.youtube.com/watch?v=ZWd1tZ5SJNU
https://data-mozart.com/dynamic-filtering-in-power-bi/
https://forum.enterprisedna.co/t/dynamic-measures-with-switch-and-customizing-a-matrix/11322/3


and why SWITCH / IF does not work in your DAX and use of dynamic measures, please go through this link:
https://community.fabric.microsoft.com/t5/Desktop/Return-Filter-from-Switch-Statement-DAX/td-p/2335608 


Time intellegence: 

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

 

If it were me, I would define multiple measures and, optionally, hide them from the report view. You can then switch the measure based on the filter of your choice.

Thanks

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.