Filter to only show unique values

[Roym]

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

[selimovd · Accepted 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

[Roym]

Great! this works perfectly, thanks!!