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 All,
I am trying to figure out how to count the values inside a matrix in a measure.
My raw data looks like this. I have a large number of values and a large number of categories that the Category Type can be.
VALUE# | Category Type |
Value1 | Category 1 |
Value1 | Category2 |
Value2 | Category3 |
Value 2 | Category 3 |
Value 3 | Category 30 |
I would like to be able to identify if any duplicates exist per Value.
For example, in a matrix it would look like:
Category1 | Category2 | CategoryN | |
Value1 | Count of category1 | Count of category2 | Count of categoryN |
Value2 | Count of category1 | Count of category2 | Count of categoryN |
Value3 | Count of category1 | Count of category2 | Count of categoryN |
So that my final data can look like:
Is there any duplicate? | |
Value 1 | No |
Value2 | Yes |
Value3 | No |
I am interested in wrapping this up to a measure rather than show the visualization of the matrix in the report.
Solved! Go to Solution.
Note your example data has spaces in it that result in no duplicates. Once those are removed in query, this measure expression works in a table visual with the Value# column to get your desired result.
Duplicates =
VAR summary =
SUMMARIZE (
CatTypes,
CatTypes[Category Type],
"@count", COUNT ( CatTypes[Category Type] )
)
RETURN
IF (
COUNTROWS (
FILTER (
summary,
[@count] > 1
)
) > 0,
"Yes",
"No"
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Note your example data has spaces in it that result in no duplicates. Once those are removed in query, this measure expression works in a table visual with the Value# column to get your desired result.
Duplicates =
VAR summary =
SUMMARIZE (
CatTypes,
CatTypes[Category Type],
"@count", COUNT ( CatTypes[Category Type] )
)
RETURN
IF (
COUNTROWS (
FILTER (
summary,
[@count] > 1
)
) > 0,
"Yes",
"No"
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat
Thank you for your reply.
I am actually interested in the duplicates only. I would like to be able to identify those because they mean 2 failures have occurred.
If only one failure occures(does NOT have a duplicate), I would like to exclude that.
Just change the "No" part of the IF to BLANK(). Once you do that rows that do not have duplicates will automatically filtered out of the visual.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @eryan123
Measures only return scalars, not tables. You can create a calculated table:
NewTable =
ADDCOLUMNS (
DISTINCT ( Table1[Value] ),
"IsThereDuplicate",
VAR dupsFound_ =
CALCULATE ( DISTINCTCOUNT ( Table1[Value] ) )
> CALCULATE ( DISTINCTCOUNT ( Table1[Value] ) )
RETURN
IF ( dupsFound__, "Yes", "No" )
)
I would recommend a visual with a measure though
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB
Thank you for your reply.
Your calculated table is not referencing the categories. As of now, all the Values are showing that they have duplicates.
The Values and Duplicates are also not the only columns in the table that I am referencing.
Any recommendations?
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |