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
Anonymous
Not applicable

How to count DISTINCT IDs Grouping By ID where all entries have a value of 1 for a specific field?

Hi,

I have a, simplified, dataset like below:

Table: Validation
ID                   Package               Value
122                1                           1
122                2                           1

122                3                           0

122                4                           1

24                  1                           1

24                  2                           1

24                  3                           1

24                  4                           1

 

I require a measure to be able to count the DISTINCT IDs filtering out any IDs that has a value of 0 amongst its entries.

So for the above example the measure count would equal 1 as it only ID 24 that has all its entries having a value of 1. ID 122 has a single entry containg the value 0 and therefore that ID (122) must not be included in the DISTINCT count.

 

How do I do this please?
Thanks.

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey,

 

I think this measure will do what you are looking for:

cnt Of IDs = 
var IDsToExclude = 
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Table1'
                    ,Table1[ID]
                )
                ,"MinValue", CALCULATE(MIN('Table1'[Value]))
            )
            ,[MinValue] = 0
        )
        ,"ID", 'Table1'[ID]
    )
return
COUNTROWS(
    EXCEPT(
        VALUES(Table1[ID])
        ,IDsToExclude
    )
)

the variable IDsToExclude contains a table with a single column of the unique IDs.
Then EXCEPT purges the IDs from the list of all IDs, the remaining rows are simlpy counted.

 

Hopefully this is what you are looking for.

Regards,
Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

balaganeshv2201
Frequent Visitor

Measure = COUNTROWS(filter(GROUPBY(Table1,Table1[id],"min_val",MINX(CURRENTGROUP(),Table1[value])),[min_val]=1))

 

it will display the count of uniwue rows

 

 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

This measure works as well

 

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Table1[ID]),[ID],"ABCD",MIN(Table1[Value]),"EFGH",MAX(Table1[Value])),[ABCD]=[EFGH]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
balaganeshv2201
Frequent Visitor

Measure = COUNTROWS(filter(GROUPBY(Table1,Table1[id],"min_val",MINX(CURRENTGROUP(),Table1[value])),[min_val]=1))

 

it will display the count of uniwue rows

 

 

Anonymous
Not applicable

@balaganeshv2201

This works too. Good to know an alternative that I can use.

 

Of the two solutions I've been given - is one better than the other, and why?

Thanks.

Hey @Anonymous,

 

asking if one solution is better, this is difficult to answer.

 

From a performance point of view the solution of @balaganeshv2201 is faster (I tested it with datasets of  1, 10, and 100 million rows.

 

GROUPBY() has it's own subtleties you can read about it

here: https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

here you will find something about using SUMMARIZE/ADDCOLUMNS as I did in my solution:https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

As always, it depends. Depending on the subtleties of your model and reporting requirements, I would stick with my solution. If your requirements are straightforward and the reporting requirements are simple (meaning no report and page level filters, go with the formula @balaganeshv2201 provided.

 

For a model of 100million rows (40million unique ID,~2.5 packages on average) using GROUPBY is 6times faster than SUMMARIZE/ADDCOLUMNS

 

Regards,

Tom

 

I did the performance comparison with DAX Studio available here:

https://www.sqlbi.com/tools/dax-studio/



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thank you!

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

Hey,

 

I think this measure will do what you are looking for:

cnt Of IDs = 
var IDsToExclude = 
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Table1'
                    ,Table1[ID]
                )
                ,"MinValue", CALCULATE(MIN('Table1'[Value]))
            )
            ,[MinValue] = 0
        )
        ,"ID", 'Table1'[ID]
    )
return
COUNTROWS(
    EXCEPT(
        VALUES(Table1[ID])
        ,IDsToExclude
    )
)

the variable IDsToExclude contains a table with a single column of the unique IDs.
Then EXCEPT purges the IDs from the list of all IDs, the remaining rows are simlpy counted.

 

Hopefully this is what you are looking for.

Regards,
Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens

It's exactly what I am looking for. Many thanks!

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.