Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello.
I have a data set that looks like this:
Task Config. Time Task 1 A 20 Task 1 A 60 Task 1 B 214 Task 2 A 8 Task 2 B 215 Task 3 A 67 Task 3 B 21 Task 3 B 57
If I create a measure like "= COUNTROWS(Tablename)" it will return 8, because there are 8 rows. What I would like is to only include unique Task + Config combinations in the count, meaning that "Task 1 - A" and "Task 3 - B" are only counted once, instead of twice (because they have 2 different time values, which I would like to not be included in the count) and that the returned value is 6, not 8.
I played around a little with COUNTX but was not able to achieve what I am trying.
Thank you for your help,
-L
Solved! Go to Solution.
Hey,
maybe this measure provides what you are looking for.
Countrows Summarize = COUNTROWS( SUMMARIZE('Table1' ,Table1[Task] ,Table1[Config.] ) )
It results to 6.
Using SUMMARIZE allows to create a table using just the columns you want.
You can also use
ADDCOLUMNS(
SUMMARIZE(...)
,"NewColumnName 1", Expression
,...
)
For more examples using SUMMARIZE and ADDCOLUMNS please have a look here:
https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/
Hopefully this is what you are looking for
Regards
Tom
Hey,
maybe this measure provides what you are looking for.
Countrows Summarize = COUNTROWS( SUMMARIZE('Table1' ,Table1[Task] ,Table1[Config.] ) )
It results to 6.
Using SUMMARIZE allows to create a table using just the columns you want.
You can also use
ADDCOLUMNS(
SUMMARIZE(...)
,"NewColumnName 1", Expression
,...
)
For more examples using SUMMARIZE and ADDCOLUMNS please have a look here:
https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/
Hopefully this is what you are looking for
Regards
Tom
You could add a calculated column that does a CONCATENATE of your two columns and then write a measure that does a DISTINCTCOUNT on that new column.
Measure =
COUNTROWS ( SUMMARIZECOLUMNS ( Table1[Task], Table1[Config.] ) )
this measure creates a table containg all distinct values of the task and config columns and then returns the number of rows in said table
edit: Tom Martens post does the same method just with summarize and add columns instead of summarizecolumns. They should produce the same result and perform the same, summarizecolumns just has simpler syntax.
https://www.sqlbi.com/articles/introducing-summarizecolumns/ to read more about this
Hi @Greg_Deckler, @TomMartens & @Anonymous!
Thank you all for your super quick replies and the proposed solutions - much appreciated. I tried all of them and they work just fine and provide me with what I need. I ended up using TomMartens proposal, it seemed like the most simple solution for my actual data set.
Thanks again and have a nice day!
-L
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |