Forum Discussion

atul9063's avatar
atul9063
Icon for Helper III rankHelper III
7 years ago
Solved

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.

 

 

 

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]))
)
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi atul9063,

     

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

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

     

    Best regards,

    Yuliana Gu

    • atul9063's avatar
      atul9063
      Icon for Helper III rankHelper III
      7 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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

  • huguestremblay's avatar
    huguestremblay
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
      huguestremblay
      Icon for Helper II rankHelper II
      5 years ago

      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.