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 All,
I am having a problem doing something in Power BI that should be quite easy but is proving to be quite difficult. Here is the setup:
The colRatio column does not match the Ratio measure, which means the Est Scale column is incorrect. Analysis shows that the number colRatio does display (800.903) is the correct result if there were no page level filters.
Am I using the ALLSELECTED method correctly? How do I set the colRatio column to the Ratio measure value, or at least recreate the calculation that make the Ratio measure value for each row in the colRatio column?
Any help would be appreciated and if you need any more information, I would be happy to provide it. Thanks.
Solved! Go to Solution.
Sorry, it isn't very clear what these calculations are supposed to represent, so it's a bit like shooting in the dark.
This might be what you're looking for but I can't really tell from the information provided:
Est Scale =
DIVIDE (
SUM ( TableD[Sample Weight] ),
CALCULATE ( [Ratio], ALLSELECTED ( TableD ) )
)
ALLSELECTED is not as simple as it might seem at first. Read this for the gory details:
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
That said, your issue might simply be that you're expecting a calculated column to be sensitive to report filters. Calculated columns cannot be dynamically responsive to user interaction via slicers and filters since it's only computed once each time your data model is first loaded or refreshed, not in response to what the user is doing. Measures, on the other hand, are intended to be dynamically responsive.
So is there any way I can get a calculated column to be dynamically responsive?
No. For the reasons I pointed out, this is fundamentally impossible.
However, measures are meant to be dynamic and I don't see any reason why a measure wouldn't work just as well as a calculated column for your visual.
The Est Scale column is the row value Sample Weight divided by the measure Ratio. Is there a way to use a calculated value in each row without using a new column?
That can be a measure too. See if this works:
Est Scale = SUM ( TableD[Sample Weight] ) / [Ratio]
Unfortunately, that returns a value as if that row was the only row in the table. Same thing as if I use a SUMX(TableD, TableD[Sample Weight]) / [Ratio].
Sorry, it isn't very clear what these calculations are supposed to represent, so it's a bit like shooting in the dark.
This might be what you're looking for but I can't really tell from the information provided:
Est Scale =
DIVIDE (
SUM ( TableD[Sample Weight] ),
CALCULATE ( [Ratio], ALLSELECTED ( TableD ) )
)
Thanks. That worked.
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 |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
48 | |
45 | |
19 | |
16 |