Filter a Slicer by measure

[Damian_CT_Nom]

Hello,
I am using simple measures such as these:

GZP_unique = 
DISTINCT(
    UNION(
        VALUES(Tickets_Hut[gzp_name]),
        VALUES(Total_Geometries[gzp])
    )
)

Variant_Rules_unique = 
DISTINCT(
    UNION(
        VALUES(Tickets_Hut[Variant_Rules]),
        VALUES(Total_Geometries[Variant_Rules]),
        VALUES(Tickets_Plat[Variant_Rules])
    )
)

to later filter a couple of slicers. The first one works as intended but I'm getting an error on the second one:

To have some context, 

I have,  3 data tables:

• Tickets_Hut
• Tickets_Plat
• Total_Geometries

I have 2 master tables:

• GZP
• Variant_Rules

GZP is present in Tickets_Hut and Total_Geometries, Variant_Rules is present in all three data tables. Both have direct 1 to many relations to the data tables. 

I'm doing the same on both Slicers, add a slicer with a field from the master table, drag the measure I need into the filter panel and check "is not blank".  

What am I missing?

[Anonymous]

Hi @Damian_CT_Nom ,

Thanks for reaching out to us with your problem. According to your description, it seems that you want to create two measures and use them to filter the slicers which the field from the master tables. However, the return values of your measures are a list of values not a scalar value just as @ValtteriN said. You can follow the steps below to get it:

1. Create two measures as below to replace the original ones:

GZP_flag =
VAR _tab =
    DISTINCT (
        UNION ( VALUES ( Tickets_Hut[gzp_name] ), VALUES ( Total_Geometries[gzp] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( 'GZP'[slicerfield] ) IN _tab, 1, 0 )

Variant_Rules_flag =
VAR _tab =
    DISTINCT (
        UNION (
            VALUES ( Tickets_Hut[Variant_Rules] ),
            VALUES ( Total_Geometries[Variant_Rules] ),
            VALUES ( Tickets_Plat[Variant_Rules] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Variant_Rules'[slicerfield] ) IN _tab, 1, 0 )

2. Apply a visual level filter with the condition (GZP_flag is 1) for GZP field slicer and the condition (Variant_Rules_flag is 1) for Variant_Rules field separately

Best Regards

[Damian_CT_Nom]

Thank you for your response,
I've tried bit by bit what you suggested:

Variant_Rules_unique = 
VAR _tab =
    DISTINCT (
        UNION (
            VALUES ( Tickets_Hut[Variant_Rules] ),
            VALUES ( Total_Geometries[Variant_Rules] ),
            VALUES ( Tickets_Plat[Variant_Rules] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Variant_Rules'[manual_description] ) IN _tab, 1, 0 )

 only changed the field that I'm using on the Slicer. It doesn't throw an error but nothing shows now:

As you can see on the above image, this is a new page with no filters whatsoever other than the one you told me to use. The slicer is just blank and I know for a fact that there are many items that should be there.

This is how looks the relation, for example for Total_Geometries:

It is the same for the other two tables:

Any idea what am I missing or doing wrong?

[ValtteriN]

Hi,

The second measure is returning a table funtion. Consider this example:

Measure 13 = DISTINCT(VALUES('Calendar'[Date]))


This measure returns the same error because the result is a list of values. Here we can see the same function used as a calculated table:

If you want to include the values as a filter you can create a filter measure like this:

Measure 14 = IF(MAX('Calendar'[Date]) in DISTINCT(VALUES('Calendar'[Date])),1,0)

This measure checks if a value is in the list

Now place the measure as filter like this:
The slicer will return the values contained in the list.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Damian_CT_Nom]

thanks for your response ValtterIN,
I can't wrap my mind on how to use this with 3 different tables at once. I need to get the distinct values for each table as a list which will filter the slicer.
The master data could have 300 values, but the sum of distinct values for the 3 data tables could just have 10 values. Thats my goal, to show 10 out of the 300 values that the master table has in my Slicer.