Forum Discussion
Using SWITCH or IF for parameters required for the table in DAX Filter
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)
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:
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
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)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
sevenhills
Apologies. Divided by a common language
When I said retrofit I meant I have a measure for TY with VAR etc defined that saysSales 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 meaureSales 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
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 areIf 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