Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have calculated the budgeted Sales (4) for venue, food and beverage. I have page slicers for Department (Dept) and Sub Department (SubDept) so I have removed these filters too.
My budgeted sales value is correct. What I want to now do is only show the sales amount if 2 departments are selected but not if any of the other departments are selected.
The calculation for budgeted Sales is:
The departments are AV, Finance, HR, IT, Operations, Prop Ops, SME.
I would like to be able to show the budgeted sales amount if Operations or Prop Ops are selected but not any of the other departments.
Thank you
Solved! Go to Solution.
@CNENFRNL , I managed to get the result by adding a second measure.
I used your original answer and then added a SWITCH calculation:
Hi, @Racq , it seems the measure can't be authored in a more readable manner,
=
CALCULATE (
SUM ( pssSNAPGCCECBudgetDetail[Budget] ),
NewGLCode[Parent] IN { "Venue Hire", "Food", "Beverage" },
ALL ( NewGLCode[Dept], NewGLCode[Sub Dept] ),
NewGLCode[Account] = "4",
ALLSELECTED ( Department[Dept] )
)
It's only a suggestion from the perspective of conciseness as it's hard to author a correct measure in one shot without a close look at the relationships in the data model.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL
Thank you, I have updated the concise formula.
The part of the formula for ALLSELECTED ( Department[Dept]) should read ALLSELECTED ( NewGLCode[Dept]).
Which means that the formula does not work.
I am stuck on what information you need that will help with finding a solution. It all seems to focus around the NewGLCode[Dept]. I need to unfilter this slicer so that I can get the sales, but then apply the filter again to only get the sales if one of the two departments are selected.
The Dashboard I want to show is the budget for wages for the month. But only Operations and Prop Ops wages are to be shown as a %age of the sales.
Does the below screenshot help? The budget VFB column below should be 1,206,567 if the NewGLCode[Dept] slicer Operations or Prop Ops are selected, and be blank if not.
The relationships are
@Racq , you might want to tweak the measure this way,
=
IF (
ISFILTERED ( NewGLCode[Dept] ),
CALCULATE (
SUM ( pssSNAPGCCECBudgetDetail[Budget] ),
NewGLCode[Parent] IN { "Venue Hire", "Food", "Beverage" },
ALL ( NewGLCode[Dept], NewGLCode[Sub Dept] ),
NewGLCode[Account] = "4",
ALLSELECTED ( Department[Dept] )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL , I managed to get the result by adding a second measure.
I used your original answer and then added a SWITCH calculation:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |