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

[Steve_Scotland]

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(DATES),  DATES[Week] = Reporting_Week_TY   &&  DATES[Year] = Reporting_Year_TY

and for Period TY I am passing

ALL(DATES),    DATES[Week] <= Reporting_Week_TY   &&   DATES[Period] = Reporting_Period   &&  DATES[Year] = Reporting_Year_TY

ie

// Example of Week TY
// ***************************************************
// // The Calc bit :

VAR FilteredDates =

FILTER(  ALL(DATES), DATES[Week] = Reporting_Week_TY   &&    DATES[Year] = Reporting_Year_TY )

VAR HoldCalc =  SUMX(FilteredDates, [Sales])

RETURN
IF( HoldCalc = BLANK(), 0, HoldCalc )

// Example of Period TY
// ***************************************************
// // The Calc bit :

VAR FilteredDates =
FILTER( ALL(DATES), DATES[Week] <= Reporting_Week_TY &&  DATES[Period] = Reporting_Period && DATES[Year] =  Reporting_Year_TY)

VAR HoldCalc = SUMX(FilteredDates, [Sales])

RETURN
IF( HoldCalc = BLANK(), 0, HoldCalc)

All VAR etc happily predined.  Both meaures work quite fine in isolation

//*******************

I have various other calculations and the only difference is the dates being passed to the filter.   

**  All work.  ** 

Because I want to do many more measures, ie whether I am doing Sales Week TY or Profit Period LY - for example - typos will probably occur.

What I was wondering if I could have a VAR for Calcs_Needed such as

Calcs_Needed = "Week TY"
Calcs_Needed = "Week LY"
Calcs_Needed = "Period TY"

etc  (Very obviously one at a time, at the top of the code)

and THEN have a Switch or an IF in the VAR FilteredDates and put the Filter for each in. That way I change what I need at the very top of my Code and it determines what is passed to the SUMX(FilteredDates,

I tried a very basic test with

VAR FilteredDates =

IF ( Calcs_Needed = "Week TY",
      FILTER(  ALL(DATES), DATES[Week] = Reporting_Week_TY   &&    DATES[Year] = Reporting_Year_TY ),
      FILTER(  ALL(DATES), DATES[Week] = Reporting_Week_LY   &&    DATES[Year] = Reporting_Year_LY )
)

VAR HoldCalc = SUMX(FilteredDates, [Sales])

RETURN
IF( HoldCalc = BLANK(), 0, HoldCalc)

and a SWITCH version.

Both gave error  at the SUMX(FilteredDates

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I thought it would be a breeze and I would longer term - having taken the 'hit' of retrofitting my code - make things much more manageable

I presume I am being very naiive thinking it would work

[sevenhills]

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/23356... 

Time intellegence: 

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

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 · Accepted solution]

@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]

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]

@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]

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

[Steve_Scotland]

@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