Forum Discussion

Kratos_ZA's avatar
Kratos_ZA
Helper I
5 years ago
Solved

Count based on dynamic filter

Hi Friends,

 

Please assist me with this issue I can't seem to solve:

 

So as you can see in the table, I have customer/franchisee names and below that are the stores they have. I created a disjointed table which shows "red", "amber" and "green" which is actually the grouping of the stores stly growth eg. if < 0, "red", < 0.5, "Amber" and so forth. The slicer "display" will show only stores that falls within the selected range. 

 

The store count seems to appear irrespective of whether they are in any of the color groupings. I want to do a count of the stores that only falls within each of the respective grouping.

 

DAX used for the "Display" slicer to work is:

 

COLOR FILTER =
VAR FILTERVALUE =
SELECTEDVALUE('Table'[VALUE])
VAR STLY = [STLY GROWTH]
VAR RESULT =
SWITCH(
TRUE(),
STLY <= FILTERVALUE,STLY,
ISBLANK(FILTERVALUE),STLY,
BLANK()
)
RETURN
RESULT

 

Note that the measure "Colour Filter" is actually a YOY growth measure

 

Please let me know what code I can use for the dynamic count of stores by selecting the different colour diplay slicer.

 

Thanks all!

 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Try:

    Count of Store =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Cutsomer name] ),
    FILTER ( Table, NOT ( ISBLANK ( [COLOR FILTER] ) ) )
)

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Try:

    Count of Store =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Cutsomer name] ),
    FILTER ( Table, NOT ( ISBLANK ( [COLOR FILTER] ) ) )
)

     

    • Kratos_ZA's avatar
      Kratos_ZA
      Helper I
      5 years ago

      Thank you sir! Appreciate the help. This worked perfectly:)