Forum Discussion

rock_stage_user's avatar
rock_stage_user
Icon for Helper I rankHelper I
3 years ago

Return result based on slicer selection

I have created one measure .

Measure = IF(SELECTEDVALUE(slicer[option])="No",0,IF(SWITCH(TRUE(),NOT(VALUES(email_notification_info[domain_url])) IN VALUES(Excluded_domains[domain]),0,1),1))
 
when user clicks on yes then it will be  exclude the domains from result set and when user click on No it will show all data.
but using above measure it is not working bcos .
NOT(VALUES(email_notification_info[domain_url])) IN VALUES(Excluded_domains[domain])
this will return multiple values. and expected single value there also countrows not working
 Data is like this that is sum of user data in the column total number of email notifications
Email notification data
 

 

 

Need Help

15 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi rock_stage_user 

    Your request is not really clear but in a simple glance I can tell that your measure works fine only when the silcer selection is on "No" 

    No Options created for "Yes"

    • rock_stage_user's avatar
      rock_stage_user
      Icon for Helper I rankHelper I
      3 years ago

      Sorry, my mistake,

      Measure = Var selection = SELECTEDVALUE(slicer[option]) RETURN (SWITCH(TRUE(),selection = "Yes",CALCULATE(COUNT(email_notification_info[user_id]),email_notification_info[Excluding data from agencies and Mastercard Employees]=TRUE),selection="No",0))
      this is the one measure i have tried but given wrong result,
      and another 
      Measure = Var selection = SELECTEDVALUE(slicer[option])
      RETURN (SWITCH(TRUE(),
      selection = "Yes",countrows(FILTER(new_processor_info,new_processor_info[Excluding data from agencies and Mastercard Employees]= FALSE())),
      selection="No",0))
      not gives the actual sum/count
    • rock_stage_user's avatar
      rock_stage_user
      Icon for Helper I rankHelper I
      3 years ago

      can you pls let me know how should i proceed for yes option with if any aggregation over there,like sum,count

      i have tried diff diff ways bit still not giving correct answer

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi rock_stage_user ,

    According to your description, I create a sample.

    Based on your formula, it can get the correct result.

    The result are different is because the conditions in the two formula are different.

    If you can't return correct result, please check two points:

    1.If the column format of Excluding data from agencies and Mastercard Employees is True/false.

    2.Add "ALL" in the formula like this:

    Formula1 =
VAR selection =
    SELECTEDVALUE ( slicer[option] )
RETURN
    (
        SWITCH (
            TRUE (),
            selection = "Yes",
                COUNTROWS (
                    FILTER (
                        ALL ( new_processor_info ),
                        new_processor_info[Excluding data from agencies and Mastercard Employees]
                            = FALSE ()
                    )
                ),
            selection = "No", 0
        )
    )

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • rock_stage_user's avatar
      rock_stage_user
      Icon for Helper I rankHelper I
      3 years ago

      Thanks for your reply, but i want to show all records if i am click on No and when i am click on yes only true related records it will show in the report.

    • rock_stage_user's avatar
      rock_stage_user
      Icon for Helper I rankHelper I
      3 years ago

      if you see below records

      and report

       

      if i am clicking on yes then it will show count 1 and if i am click on No then result must be all that is in case here is 2 so pls let me know how we can write meaure or something other ways to do it

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi rock_stage_user ,

        According to your description, here's my solution. Create a measure:

        Measure =
IF (
    SELECTEDVALUE ( slicer[option] ) = "Yes",
    COUNTROWS (
        FILTER (
            new_processor_info,
            new_processor_info[Excluding data from agencies and Mastercard Employees]
                = TRUE ()
        )
    ),
    IF (
        SELECTEDVALUE ( slicer[option] ) = "No",
        COUNTROWS ( 'new_processor_info' )
    )
)

        Get the correct result:

          

        I attach my sample below for your reference.

         

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi rock_stage_user ,

    I have received your private message, as we are only working on the forum, we can only provide help on the forum, if you need to connect over a call, you can create a support ticket in Power BI, which needs a Pro license. Certainly, you can connect here in the forum.

     

    Best Regards,
    Community Support Team _ kalyj

    • rock_stage_user's avatar
      rock_stage_user
      Icon for Helper I rankHelper I
      3 years ago

      I have following report

      It contains 604 registred user before applying any filter. after apply filter it shows count 229 which is correct, 

      but when i am selecting no it shows other records which is satisfying the condition , but i want all records when i am selecting No as per first screenshot

      I have created that filter using one column which is excluding the domains from result set.

      so pls let me know how should i proceed.

       

       

       

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi rock_stage_user ,

        Based on your snapshot, what you selected in the slicer isn't "Yes" or "No", but the True/False column from the table. A column can only filter the table exactly based on the value.

        If you want to custom the slicer, you should create a new table like this:

        Then create a measure.

        Measure =
IF (
    ISFILTERED ( slicer[option] ),
    IF (
        SELECTEDVALUE ( slicer[option] ) = "Yes"
            && MAX ( new_processor_info[Excluding data from agencies and Mastercard Employees] ) = "TRUE",
        1,
        IF ( SELECTEDVALUE ( slicer[option] ) = "No", 1 )
    ),
    1
)

        Put the measure in the fact visual filter and set to 1, then the created Yes/No slicer can filter as expected:

        I attach my sample below for your reference.

         

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.