Forum Discussion

Roym's avatar
Roym
Helper IV
4 years ago
Solved

Filter to only show unique values

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 1Medium
Item 2Low
Item 2Low
Item 2Low
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    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

     

2 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    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's avatar
      Roym
      Helper IV
      4 years ago

      Great! this works perfectly, thanks!!