Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

measure to check if value is selected in filter

Dear all,

 

I have a filter slicer on product class with 4 options and option 'all', where multiple selection is possible.

Now I would like to create a measure to check if a certain value is selected in the slicer.

How is the syntax for this measure ?

I have an idea which only considers the option that one single product is chosen.  

productselection =
VAR Class =
SELECTEDVALUE ( '3_Auswahl_Product'[Product_Names])
RETURN
SWITCH (
TRUE ();
Class = "All";TRUE();
Class = "A";If([Product Class as Number]=1;TRUE();FALSE());
Class = "B";If([Product Class as Number]=2;TRUE();FALSE());
Class = "C";If([Product Class as Number]=3;TRUE();FALSE());
Class = "D";IF([Product Class as Number]=4;TRUE();FALSE()))
 
But how about selecting A,B,C, and set all data entries to true which are product class A,B or C ?
 
Thanks,
Susanne
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a table which has no relationship with other tables,

Add a index column,

Capture7.JPGCapture8.JPG

Create two measures

Measure = CONCATENATEX(ALLSELECTED('Table 2'[Class slicer]),'Table 2'[Class slicer],",")

Measure 2 =
IF (
    ISFILTERED ( 'Table 2'[Class slicer] ),
    IF (
        SEARCH ( "ALL", [Measure], 1, 0 ),
        IF ( MAX ( 'Table 1'[class] ) IN { "A", "B", "C", "D" }, TRUE (), FALSE () ),
        IF (
            SEARCH ( MAX ( 'Table 1'[class] ), [Measure], 1, 0 ) >= 1
                && (
                    (
                        MAX ( 'Table 1'[class] ) = "A"
                            && MAX ( 'Table 1'[number] ) = 1
                    )
                        || (
                            MAX ( 'Table 1'[class] ) = "B"
                                && MAX ( 'Table 1'[number] ) = 2
                        )
                        || (
                            MAX ( 'Table 1'[class] ) = "C"
                                && MAX ( 'Table 1'[number] ) = 3
                        )
                        || (
                            MAX ( 'Table 1'[class] ) = "D"
                                && MAX ( 'Table 1'[number] ) = 4
                        )
                ),
            TRUE (),
            FALSE ()
        )
    ),
    FALSE ()
)

Capture5.JPGCapture6.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a table which has no relationship with other tables,

Add a index column,

Capture7.JPGCapture8.JPG

Create two measures

Measure = CONCATENATEX(ALLSELECTED('Table 2'[Class slicer]),'Table 2'[Class slicer],",")

Measure 2 =
IF (
    ISFILTERED ( 'Table 2'[Class slicer] ),
    IF (
        SEARCH ( "ALL", [Measure], 1, 0 ),
        IF ( MAX ( 'Table 1'[class] ) IN { "A", "B", "C", "D" }, TRUE (), FALSE () ),
        IF (
            SEARCH ( MAX ( 'Table 1'[class] ), [Measure], 1, 0 ) >= 1
                && (
                    (
                        MAX ( 'Table 1'[class] ) = "A"
                            && MAX ( 'Table 1'[number] ) = 1
                    )
                        || (
                            MAX ( 'Table 1'[class] ) = "B"
                                && MAX ( 'Table 1'[number] ) = 2
                        )
                        || (
                            MAX ( 'Table 1'[class] ) = "C"
                                && MAX ( 'Table 1'[number] ) = 3
                        )
                        || (
                            MAX ( 'Table 1'[class] ) = "D"
                                && MAX ( 'Table 1'[number] ) = 4
                        )
                ),
            TRUE (),
            FALSE ()
        )
    ),
    FALSE ()
)

Capture5.JPGCapture6.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hey @v-juanli-msft ,

 

it worked perfectly fine,

thank you very much!

 

Kind regards

Susanne

gckcmc
Resolver I
Resolver I

Hi Suzanne,

 

Your code chooses one type, you're correct.  I'm sure there are several solutions here, but one to think about is this:

 

use an "OR" condition and restructure switch accordingly.  You'll also need to think about your return type, as right now your return type is giving the class type as singular.  If the answer can be multiple classes, then how do you want the return type to be?  

 

A

AB

ABC

?

 

if so, you'll have to permute (or append) the class types to address.  For example:

ProdClass=1, answer is A

ProdClass=2, answer is B

ProdClass is 1&2, answer is AB;  is your prod class going to be 1&2, 12, 1+2, etc.?  your inputs will change.  Therefore your outputs will change too.  A, AB, AC, BC, ABC....etc.

 

Here's a link on the "OR" to help you think about it:

https://community.powerbi.com/t5/Desktop/Multiple-Criteria-Filter/td-p/136138

 

cheers,

glenn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.