Return result based on slicer selection

[rock_stage_user]

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

 

[v-yanjiang-msft]

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]

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]

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.

[rock_stage_user]

max is not work with boolean type

[v-yanjiang-msft]

Hi @rock_stage_user ,

Sorry I forgot to mention, in order to reference the True/False column, I change the data type to text.

Best Regards,
Community Support Team _ kalyj

[rock_stage_user]

I have created one column as a check with text type TRUE/FALSE based on the true/false column but still its not working i have added measure also in view level.

is there i am missing something?

[v-yanjiang-msft]

Hi @rock_stage_user ，

You should put the visual filter on the table visual or map visual, but not the slicer.

Best Regards,
Community Support Team _ kalyj

[rock_stage_user]

I have added but still not working,

Measure_3 =

IF(ISFILTERED(slicer[option]),IF (

SELECTEDVALUE ( slicer[option] ) = "Exclude the data from Agencies and Mastercard employees" && MAX(email_notification_info[Test_check]) = "Yes",1,

IF (

SELECTEDVALUE ( slicer[option] ) = "Show all the data including Agencies and Mastercard employees",

1

)

),1)

applied this measure on filter

let me know what is wrong it is not working at all in this report

[v-yanjiang-msft]

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]

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]

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.

[rock_stage_user]

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.

[aj1973]

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]

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

[rock_stage_user]

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