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 there,
I have 2 models where my ALLEXCEPT calculation works in one but not the other.
Screen shots below of the model that does not work.
See below my calculation....
For your reference:
eeCO_Desc - renamed to Company
eeBR_Desc - renamed to Dealership Location
eeDP_Desc - renamed to Deparment
eeFR_Desc - renamed to Franchise
I want this to filter down to the Franchise level which it doesn't appear to do.
Please refer to the yellow highlights...
The "New" line shows MTD units of 29,697, however the result from the "Check" formula is 1320659 (where I want it to show 29,697)
Similarly, the "Subaru" line shows MTD units of 9,169 and the "Check" formula produces 283654 (where I want it to show 9,169) etc...
I have used the same logic from my other model that works correctly, but for some reason it doesn't work here.
Any help would be appreciated.
Thank you.
Solved! Go to Solution.
Hi, @melanie-g ;
You can replace Allexcept() with Filter(allselected('table'),[group]=max('table'[group]))
Check =
CALCULATE (
SUM ( [MTD] ),
FILTER (
ALLSELECTED ( 'table' ),
[eeCO_Desc] = MAX ( [eeCO_Desc] )
&& [eeBR_Desc] = MAX ( [eeBR_Desc] )
&& [eeDP_Desc] = MAX ( [eeDP_Desc] )
&& [eeFR_Desc] = MAX ( [eeFR_Desc] )
)
)
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @melanie-g ;
You can replace Allexcept() with Filter(allselected('table'),[group]=max('table'[group]))
Check =
CALCULATE (
SUM ( [MTD] ),
FILTER (
ALLSELECTED ( 'table' ),
[eeCO_Desc] = MAX ( [eeCO_Desc] )
&& [eeBR_Desc] = MAX ( [eeBR_Desc] )
&& [eeDP_Desc] = MAX ( [eeDP_Desc] )
&& [eeFR_Desc] = MAX ( [eeFR_Desc] )
)
)
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try rewriting your measure to use ALL/VALUES instead of ALLEXCEPT. The article below explains the differences between these two approaches. If this doesn't solve it, please provide a sample pbix using one of the file services like OneDrive.
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Proud to be a Super User!
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |