Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Additional filter in dax

Hi, 

I am using "ISINSCOPE" to use hierarchy on axis of the visual.

 

Category Avg =
var p =
SELECTEDVALUE('CFS'[ClientNumber],BLANK())
var PAvgRating =
CALCULATE(
COUNT('PFS'[Rating]),'PFA'[ClientNumber] <> p
)
/
SWITCH(True(),
   ISINSCOPE('PFS'[Category]),
          CALCULATE(
                DISTINCTCOUNT('PFS'[ClientNumber]), 'PFS'[ClientNumber] <> p,
                All('PFS'[Rating],'PFS'[rating Sort],'PFS'[Category],'PFS'[Category Sort])),
  ISINSCOPE('PFS'[Sub Category]),
         CALCULATE(
                DISTINCTCOUNT('PFS'[ClientNumber]), 'PFS'[ClientNumber] <> p,
                All('PFS'[Rating],'PFS'[rating Sort], 'PFS'[Sub Category],'PFS'[Sub Category Sort])),
1
)
Return
           PAvgRating
 
This dax is giving correct values at category level but when drill down from a particular category to a next level 'Sub Category', it gives wrong values. The probably reason, it passes an additional filter from the category when drill down from 'Category' to 'Sub Category' level.
 
Please help what to change in the above dax to consider the additional filter value at 'Sub Category' level.
 
Thanks
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please try to update the formula of the measure [Category Avg] as below, the part with red font is updated ones...

    Category Avg =
    VAR p =
        SELECTEDVALUE ( 'CFS'[ClientNumber], BLANK () )
    VAR PAvgRating =
        CALCULATE ( COUNT ( 'PFS'[Rating] ), 'PFA'[ClientNumber] <> p )
            / SWITCH (
                TRUE (),
                ISINSCOPE ( 'PFS'[Category] ),
                    CALCULATE (
                        DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                        'PFS'[ClientNumber] <> p,
                        ALLEXCEPT ( 'PFS', 'PFS'[Category] )
                    ),
                ISINSCOPE ( 'PFS'[Sub Category] ),
                    CALCULATE (
                        DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                        'PFS'[ClientNumber] <> p,
                        ALLEXCEPT ( 'PFS', 'PFS'[Category], 'PFS'[Sub Category] )
                    ),
                1
            )
    RETURN
        PAvgRating

    In addition, you can refer to the following blog to get it.

    How to Calculate Percentages over Hierarchies in DAX

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

    How to upload PBI in Community

    Best Regards

4 Replies

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

    Anonymous , Change the order as category begin parent isinscope for sub category too

     

    Category Avg =
    var p =
    SELECTEDVALUE('CFS'[ClientNumber],BLANK())
    var PAvgRating =
    CALCULATE(
    COUNT('PFS'[Rating]),'PFA'[ClientNumber] <> p
    )
    /
    SWITCH(True(),
    ISINSCOPE('PFS'[Sub Category]),
    CALCULATE(
    DISTINCTCOUNT('PFS'[ClientNumber]), 'PFS'[ClientNumber] <> p,
    All('PFS'[Rating],'PFS'[rating Sort], 'PFS'[Sub Category],'PFS'[Sub Category Sort])),
    ISINSCOPE('PFS'[Category]),
    CALCULATE(
    DISTINCTCOUNT('PFS'[ClientNumber]), 'PFS'[ClientNumber] <> p,
    All('PFS'[Rating],'PFS'[rating Sort],'PFS'[Category],'PFS'[Category Sort])),

    1
    )
    Return
    PAvgRating

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak 

      It doesn't give correct values even after changing the order to sub category first then category.

      When I drill down by selecting a data point from category to sub category, it passes that category value to sub category level and applies as a filter on the visual.

      We need to consider this scenario in the DAX, so the dax can filter the values at sub category level. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        Please try to update the formula of the measure [Category Avg] as below, the part with red font is updated ones...

        Category Avg =
        VAR p =
            SELECTEDVALUE ( 'CFS'[ClientNumber], BLANK () )
        VAR PAvgRating =
            CALCULATE ( COUNT ( 'PFS'[Rating] ), 'PFA'[ClientNumber] <> p )
                / SWITCH (
                    TRUE (),
                    ISINSCOPE ( 'PFS'[Category] ),
                        CALCULATE (
                            DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                            'PFS'[ClientNumber] <> p,
                            ALLEXCEPT ( 'PFS', 'PFS'[Category] )
                        ),
                    ISINSCOPE ( 'PFS'[Sub Category] ),
                        CALCULATE (
                            DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                            'PFS'[ClientNumber] <> p,
                            ALLEXCEPT ( 'PFS', 'PFS'[Category], 'PFS'[Sub Category] )
                        ),
                    1
                )
        RETURN
            PAvgRating

        In addition, you can refer to the following blog to get it.

        How to Calculate Percentages over Hierarchies in DAX

        If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

        How to upload PBI in Community

        Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

    Best Regards