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.
I have a table in the following format as shown in the table below. Now I want to make a table in my report that only show unique 'items' that don't have a medium classifiction. So based on the below example I only want to see 'Item 2' as that one does not have a medium classification. Anyone an idea on how I could achieve this?
Item 1 | Low |
Item 1 | Medium |
Item 2 | Low |
Item 2 | Low |
Item 2 | Low |
Solved! Go to Solution.
Hey @Roym ,
you can create a mesure that is checking if the item in each row contains a "Medium" value. Then you can filter the visual by that measure.
I created the following measure:
ItemsWitoutMedium =
VAR vFilteredTable =
FILTER (
ADDCOLUMNS (
-- Get unique values from [Item]
VALUES ( myTable[Item] ),
-- Count for each item the number of rows that contain "Medium"
"@HasMediumVAlue", CALCULATE ( COUNTROWS ( myTable ), myTable[Classification] = "Medium" )
),
-- Filter for [Items] without "Medium" values
[@HasMediumVAlue] = BLANK ()
)
RETURN
-- return a 1 if the item has no "Medium"
COUNTX ( vFilteredTable, IF ( [@HasMediumVAlue] = BLANK (), 1) )
And then you can filter the visual:
I uploaded my example file to this post.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hey @Roym ,
you can create a mesure that is checking if the item in each row contains a "Medium" value. Then you can filter the visual by that measure.
I created the following measure:
ItemsWitoutMedium =
VAR vFilteredTable =
FILTER (
ADDCOLUMNS (
-- Get unique values from [Item]
VALUES ( myTable[Item] ),
-- Count for each item the number of rows that contain "Medium"
"@HasMediumVAlue", CALCULATE ( COUNTROWS ( myTable ), myTable[Classification] = "Medium" )
),
-- Filter for [Items] without "Medium" values
[@HasMediumVAlue] = BLANK ()
)
RETURN
-- return a 1 if the item has no "Medium"
COUNTX ( vFilteredTable, IF ( [@HasMediumVAlue] = BLANK (), 1) )
And then you can filter the visual:
I uploaded my example file to this post.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Great! this works perfectly, thanks!!
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |