Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kratos_ZA
Helper I
Helper I

Count based on dynamic filter

Hi Friends,

 

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

 

Kratos_ZA_0-1621522283698.png

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!

 

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Try:

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

Try:

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.