Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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)
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:
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 says
~~~~~~~~
and a COMPLETELY DIFFERENT meaure
~~~~~~~~~
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 !
Thanks for you explanation! Glad to hear it is all working out.
@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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
92 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |