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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
UserSam123
Helper I
Helper I

Distinctcount with sum filter

Hello everyone,

 

I need your help with the following problem.
In the table below I would like to create a measure that gives me a separate count when the sum of the number column is less than 20.

 

UserSam123_0-1701257204449.png


In this case I should have a thumbnail that displays 2, because the sum of "aaa" and "ccc" is less than 20.

What's more, the Date column is also a page slicer in the report, so will the measurement be dynamic depending on the choice in the slicer? All data are in the same table.

 

1 ACCEPTED SOLUTION
UserSam123
Helper I
Helper I

For your information, I succeeded in doing this:

 

VAR __tmpTable = SUMMARIZE(Table,Table[Name] ,"__Brands",SUM(Table[Quantity]))
RETURN CALCULATE(DISTINCTCOUNT(Table[Name]), FILTER(__tmpTable, [__Brands]<20))

View solution in original post

6 REPLIES 6
Dangar332
Memorable Member
Memorable Member

hi, @UserSam123 

try below

just adjust your table name

output = 
var a =ADDCOLUMNS(
        SUMMARIZE('Table','Table'[date],'Table'[name]),
         "d",CALCULATE(SUM('Table'[number]))
        )
RETURN
COUNTX(FILTER(a,[d]<20),'Table'[name])

 

UserSam123
Helper I
Helper I

For your information, I succeeded in doing this:

 

VAR __tmpTable = SUMMARIZE(Table,Table[Name] ,"__Brands",SUM(Table[Quantity]))
RETURN CALCULATE(DISTINCTCOUNT(Table[Name]), FILTER(__tmpTable, [__Brands]<20))
123abc
Community Champion
Community Champion

To create a measure that gives you a separate count when the sum of the number column is less than 20, you can use the following DAX formula:

Measure = CALCULATE(DISTINCTCOUNT(Table[Name]), SUM(Table[Number]) < 20)

This formula uses the CALCULATE function to filter the table based on the condition that the sum of the Number column is less than 20. The DISTINCTCOUNT function then counts the number of unique values in the Name column that meet this condition.

Regarding your question about the page slicer, the measure will be dynamic depending on the choice in the slicer as long as all the data is in the same table.

I hope this helps! Let me know if you have any further questions.

Hello,

Thanks for the feedback, but I've already used this formula and it didn't work, which is why I'm coming to you.

 

UserSam123_0-1701264014115.png

 

I use this one and it returns null. When I use yours, it sends me this error message: True/False expression doesn't specify a column

 

I’m sorry to hear that the formula I provided didn’t work for you. The error message “True/False expression doesn’t specify a column” usually occurs when each True/Fal...1.

Based on the screenshot you provided, it looks like you are using an IF statement in your formula. If this is the case, you may need to wrap your IF statement in a FILTER function to fix the issue 1. Here’s an example of how you can modify the formula:

Measure = CALCULATE(DISTINCTCOUNT(Table[Column]), FILTER(Table, SUM(Table[Number]) < 20))

This formula uses the FILTER function to filter the table based on the condition that the sum of the Number column is less than 20. The DISTINCTCOUNT function then counts the number of distinct values in the Column column for the filtered table.

Regarding your question about the Date column being a page slicer, the measure will be dynamic depending on the choice in the slicer as long as all the data is in the same table.

I hope this helps! Let me know if you have any further questions.

I don't understand why you're asking me to change my formula for yours when there's absolutely no change between mine and yours.
What's more, this formula doesn't work, it returns "null".

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors