cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobThrive Regular Visitor
Regular Visitor

Count number of rows each value appears - influenced by date slider

Hello,

I've tried so many variations I've found on this forum and stackoverflow. None appear to work for me (and I'm lacking DAX experience to manipulate them to work how I need, sorry for that)

 

I have a table like the example below, and I am trying to create a card visual to show the distinct count of reference if the number of rows for that reference is over X

 

For example

User selects between dates 2019-05-01 and 2019-05-31

Reference count >= 2       <-- this is not user selectable, so can be hardcoded into formula.

 

Reference    |   Date         |   

-----------------------------

ABCDE        | 20190501   |
ABCCC        | 20180202   |
ABDDD       | 20190502   |

ABCCC        | 20190503   |

ABCCC        | 20190504   |

ABDDD       | 20180101   |

AB123         | 20190520   |

AB123         | 20190521   |

AB123         | 20190522   |

 

Results to count

------------------

ABCCC     <-- This has 2 rows and so gets through the filtering

AB123      <-- this has 3 rows and so gets through the filtering

 

The card will now show the number 2 because I am looking to count the number of distinct reference values. This will change each time the user changes the date range.

 

Hope my explaination makes sense.

Thanks for any support.

 

 

ps. I have posted a similar message before, ( https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-Counting-Observations-by-Reference-ch... ) however this solution works when I want to show the results in a table because the table can show another column which is full of one's (1) and it shows the total at the bottom. It doesn't work when I want to use a card visual.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count number of rows each value appears - influenced by date slider

Hi @RobThrive 

Try this measure

Measure =
VAR RefCount_ = 2 // Modify as necessary
RETURN
    SUMX (
        DISTINCT ( Table1[Reference] ),
        INT ( CALCULATE ( COUNT ( Table1[Date] ) ) >= RefCount_ )
    )

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

   

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Count number of rows each value appears - influenced by date slider

Hi @RobThrive 

Try this measure

Measure =
VAR RefCount_ = 2 // Modify as necessary
RETURN
    SUMX (
        DISTINCT ( Table1[Reference] ),
        INT ( CALCULATE ( COUNT ( Table1[Date] ) ) >= RefCount_ )
    )

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

   

View solution in original post

RobThrive Regular Visitor
Regular Visitor

Re: Count number of rows each value appears - influenced by date slider

Thank you so much @AlB . I've spent all weekend trying to learn and understand how to achieve what I needed. This is the perfect solution and something for me to learn to understand!

 

Have a good week!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 148 members 1,719 guests
Please welcome our newest community members: