Forum Discussion

IF's avatar
IF
Post Prodigy
1 year ago
Solved

Values from different columns

Hi,

I am using three filter on the filter panel in the report.

  • FilterG: LevelG
  • Filter1: Level1
  • Filter2: Level2

 

As you can see at the file, there are three different cards. However, I want to use only one card.

  • If the filter 1 is selected with any value, the card should the distinct count of ID, where the Main[Level1] is 3
  • If the filter 2 is selected with any value, the card should the distinct count of ID, where the Main[Level2] is 3
  • If the filter 1 and filter 2 is selected with any value, the card should the distinct count of ID, where the Main[Level2] is 3
  • If no filter is selected, then the card should the distinct count of ID, where the Main[LevelG] is 3

I want a new card that works with this logic based on the filters that I use at the filter panel

 

The link for the file is: https://we.tl/t-NtYntqSwxi

 

Need Help
  • Shivu-2000's avatar
    Shivu-2000
    1 year ago

    Hi IF 

     

    Try using this dax measure

     

    Dynamic ID Count =
    VAR Filter1Selected = NOT ISBLANK(SELECTEDVALUE(Main[Level1]))
    VAR Filter2Selected = NOT ISBLANK(SELECTEDVALUE(Main[Level2]))
    VAR FilterGSelected = NOT ISBLANK(SELECTEDVALUE(Main[LevelG]))

    RETURN
    SWITCH(
    TRUE(),
    Filter1Selected && Filter2Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level2] = 3),
    Filter1Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level1] = 3),
    Filter2Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level2] = 3),
    FilterGSelected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[LevelG] = 3),
    CALCULATE(DISTINCTCOUNT(Main[ID]), Main[LevelG] = 3) -- Default case if no filters are selected
    )

     

    And pull this on the card visual.

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Happy to help!

1 Reply

  • Shivu-2000's avatar
    Shivu-2000
    Responsive Resident
    1 year ago

    Hi IF 

     

    Try using this dax measure

     

    Dynamic ID Count =
    VAR Filter1Selected = NOT ISBLANK(SELECTEDVALUE(Main[Level1]))
    VAR Filter2Selected = NOT ISBLANK(SELECTEDVALUE(Main[Level2]))
    VAR FilterGSelected = NOT ISBLANK(SELECTEDVALUE(Main[LevelG]))

    RETURN
    SWITCH(
    TRUE(),
    Filter1Selected && Filter2Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level2] = 3),
    Filter1Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level1] = 3),
    Filter2Selected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[Level2] = 3),
    FilterGSelected, CALCULATE(DISTINCTCOUNT(Main[ID]), Main[LevelG] = 3),
    CALCULATE(DISTINCTCOUNT(Main[ID]), Main[LevelG] = 3) -- Default case if no filters are selected
    )

     

    And pull this on the card visual.

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Happy to help!