cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atul9063 Member
Member

DAX: To filter values from two slicers

Hi ,

I have created below dax to filter value  from two slicers.Issue is when i select "Active Users" from slicer [A] and "Active YoY" from slicer [B] then only Active Users show 1.I want both Active Users and Active YoY show 1.

 

 image.png

 

CategoryActiveUsers = IF(SEARCH("Active Users",MAXX(VALUES(Sheet5),Sheet5[Metric]),1,BLANK()),1,0)
CategoryYoY = IF(SEARCH("Active YoY",MAXX(VALUES(Sheet5),Sheet5[Metric]),1,BLANK()),1,0)

MeasureSelected = 
IF (
    HASONEVALUE ( Metric[A] ) ,
    SWITCH (
        FIRSTNONBLANK ( Metric[A],Metric[A] ),
        "Active Users", [CategoryActiveUsers]
    ), IF( (HASONEVALUE(YoY[B])),SWITCH(FIRSTNONBLANK(YoY[B],YoY[B]),"Active YoY",[CategoryActiveYoY]))
)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX: To filter values from two slicers

Hi @atul9063,

 

CategoryActiveUsers =
IF (
    ISERROR (
        SEARCH (
            SELECTEDVALUE ( Sheet5[Metric] ),
            IF (
                ISFILTERED ( Metric[A] ),
                CONCATENATEX ( Metric, Metric[A], "," ),
                BLANK ()
            )
        )
    ),
    0,
    1
)

CategoryYoY =
IF (
    ISERROR (
        SEARCH (
            SELECTEDVALUE ( Sheet5[Metric] ),
            IF ( ISFILTERED ( YoY[B] ), CONCATENATEX ( YoY, YoY[B], "," ), BLANK () )
        )
    ),
    0,
    1
)

MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: DAX: To filter values from two slicers

Hi @atul9063,

 

The formula for measure [MeasureSelected] could be simplified to:

MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
atul9063 Member
Member

Re: DAX: To filter values from two slicers

Hi,

 

When you unselect Active YoY and Active Users,even then Measure selected showing 1. I want to show  whenever value selected either from A or B,it should display 1 in measure selected

Community Support Team
Community Support Team

Re: DAX: To filter values from two slicers

Hi @atul9063,

 

Please modify measures as below:

CategoryActiveUsers =
IF (
    ISFILTERED ( Metric[A] )
        && NOT (
            ISERROR (
                SEARCH ( SELECTEDVALUE ( Metric[A] ), SELECTEDVALUE ( Sheet5[Metric] ) )
            )
        ),
    1,
    0
)

CategoryYoY =
IF (
    ISFILTERED ( YoY[B] )
        && NOT (
            ISERROR (
                SEARCH ( SELECTEDVALUE ( YoY[B] ), SELECTEDVALUE ( Sheet5[Metric] ) )
            )
        ),
    1,
    0
)

MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
atul9063 Member
Member

Re: DAX: To filter values from two slicers

@v-yulgu-msft Thanks  for replying back..

Now issue is coming,when I enable multiselect for both slicer and select only Active YoY and Idle YoY, then all four values showing 1.

Any help  when i multi select only those values should show 1.

Community Support Team
Community Support Team

Re: DAX: To filter values from two slicers

Hi @atul9063,

 

CategoryActiveUsers =
IF (
    ISERROR (
        SEARCH (
            SELECTEDVALUE ( Sheet5[Metric] ),
            IF (
                ISFILTERED ( Metric[A] ),
                CONCATENATEX ( Metric, Metric[A], "," ),
                BLANK ()
            )
        )
    ),
    0,
    1
)

CategoryYoY =
IF (
    ISERROR (
        SEARCH (
            SELECTEDVALUE ( Sheet5[Metric] ),
            IF ( ISFILTERED ( YoY[B] ), CONCATENATEX ( YoY, YoY[B], "," ), BLANK () )
        )
    ),
    0,
    1
)

MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)

1.PNG

Best regards,

Yuliana Gu

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