Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

ALLSELECTED ignoring page level filters

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.

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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.