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 

Belated Thanks  Have had 'issues' here where I am not getting alerts.
And today,  "Unexpected error" trying to reply.

I think I will just give in at the mo and do it the long way and - if I find an option - retrofit my 500 measures another time.  I will just stick with Sales Week TY  measure passing one set of filtered dates  and  Sales Week LY passing another set.  It works.  

 

Doing it via  IF /  SWITCH statement was a 3am thought which woke me - we have all been there! -  and wrote it down.


I spent 7 hours yesterday trying to get it to work and am quite frazzled by it. 


Old timers like me from other programming languages still imagine IF statements are   

If this,    do that   or else......    and SWITCH is for making the code look nicer.  Scalar values is a complication I hadn't thought of. 

 

Doesn't help when you read elsewhere that SWITCH returns scalar,  IF doesn't so use that instead  (epic fail - ha ha)

Thanks again for taking the time to write.  I hope query and your kind reply helps others too