Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!!
User | Count |
---|---|
134 | |
72 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |