topic Re: ALLSELECTED ignoring page level filters in DAX Commands and Tips

ALLSELECTED ignoring page level filters

Anonymous — Fri, 03 Dec 2021 19:03:01 GMT

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 datatables are TableA (which contains [Mill Number]) -> TableB (which contains [Population Number]) -> TableC (which contains [Report Period Date] -> TableD, which contains the data in the table object as follows:
- TableD[Load Table]
- TableD[Sample Weight]
- TableD[Net Scale]
- New Column[Est Scale] = TableD[Sample Weight] / New Column[colRatio]
- New Column[Diff] = TableD[Net Scale] - New Column[Est Scale]
- New Column[colRatio] = CALCULATE([Ratio], ALLSELECTED(TableD)) - I have to do this with a CALCULATE function because if I use [Ratio] directly, I get a circular error.
The following measure has also been created:
- Ratio = SUM(TableD[Sample Weight]) / SUM(TableD[Net Scale]) = 821.112

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.

Re: ALLSELECTED ignoring page level filters

AlexisOlson — Fri, 03 Dec 2021 22:51:10 GMT

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.

Re: ALLSELECTED ignoring page level filters

Anonymous — Sun, 05 Dec 2021 19:56:19 GMT

So is there any way I can get a calculated column to be dynamically responsive?

Re: ALLSELECTED ignoring page level filters

AlexisOlson — Sun, 05 Dec 2021 20:00:50 GMT

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.

Re: ALLSELECTED ignoring page level filters

Anonymous — Mon, 06 Dec 2021 14:32:05 GMT

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?

Re: ALLSELECTED ignoring page level filters

AlexisOlson — Mon, 06 Dec 2021 15:41:55 GMT

That can be a measure too. See if this works:

Est Scale = SUM ( TableD[Sample Weight] ) / [Ratio]

Re: ALLSELECTED ignoring page level filters

Anonymous — Mon, 06 Dec 2021 16:44:41 GMT

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].

Re: ALLSELECTED ignoring page level filters

AlexisOlson — Mon, 06 Dec 2021 18:15:31 GMT

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 ) ) )

Re: ALLSELECTED ignoring page level filters

Anonymous — Mon, 06 Dec 2021 20:58:02 GMT

Thanks. That worked.