DAX: To filter values from two slicers

[atul9063]

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.

 

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]))
)

[huguestremblay]

Hello,

I created a similar measure in my report and it works perfectly in a table where I list individual records as above.  However, I need to create a bar chart (or table) that shows a disctinct count of records in a given category but as soon as I change from "Don't Summarize" to "Count (Distinct)", all of the data disappears.  Anything I can do to fix that?

Thanks.

[huguestremblay]

I was able to get what I need by adding the fields being filtered to the Axis (below my date field).  They do not appear on the chart, but blank months are now showing.

[v-yulgu-msft]

Hi @atul9063,

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

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

Best regards,

Yuliana Gu

[atul9063]

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

[v-yulgu-msft]

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)

Best regards,

Yuliana Gu

[atul9063]

@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.

[v-yulgu-msft · Accepted solution]

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)

Best regards,

Yuliana Gu