Create Calculated table based on slicer selection

Question

I've a table with ID, Names and Day column as under :

+----+------+-----------------+
| ID | Name |      Days       |
+----+------+-----------------+
|  1 | A    | 30 days or less |
|  1 | A    | 30 days or less |
|  1 | A    | 30 days or less |
|  1 | A    | 31 to 60 days   |
|  2 | B    | 30 days or less |
|  2 | B    | 31 to 60 days   |
|  2 | B    | 61 to 90 days   |
|  2 | B    | 61 to 90 days   |
|  3 | C    | 90+ days        |
|  3 | C    | 61 to 90 days   |
+----+------+-----------------+

Alongside I've two slicers, coming from two different tables with such one column each

What I'm trying to do here is, based on the user slicer selection, I want the table to be filtered and only output "Name" that arent present in the first slicer selection, also the anti-join needs to be done on the ID column

So for the above slicer selection, viz. 30 days or less compare with 31 to 60 days, the results should be a table with column "Name" and just record "C" as its the only Name thats not present in the "30 days or less" but present in "31 to 60 days"

mkRabbani mkRabbani · Accepted Answer · 2020-09-17T06:47:32

I found mismatch in your explanation as you said C is there in "31 to 60 days" but actually C is not present in "31 to 60 days" in your sample data. Despite the above confusion, I tried ti figure out the process based on your explanation ignoring your sample data mismatch. If I am close to understand correctly, you can try this below steps and also can adjust things if your requirement is bit different.

Step-1: Create 2 different Iceland (non relational) table with same value belongs to your slicer. Both table will have the same value, but you just need to create 2 slicer from 2 tables and make sure there are no relation as shown below-

Step-2: Create this below measure-

show_hide = 

VAR current_name = MIN('your_base_table_name'[Name])
VAR selected_range_1 = SELECTEDVALUE('day range 1'[range])
VAR selected_range_2 = SELECTEDVALUE('day range 2'[range])

VAR chk_not_in = 
IF(
    COUNTROWS(
        FILTER(
            ALL('your_base_table_name'),
            'your_base_table_name'[Name] = current_name && 'your_base_table_name'[Days] = selected_range_1
        )
    ) = 0,
    1,
    0
)

VAR chk_in = 
IF(
    COUNTROWS(
        FILTER(
            ALL('your_base_table_name'),
            'your_base_table_name'[Name] = current_name && 'your_base_table_name'[Days] = selected_range_2
        )
    ) = 0,
    0,
    1
)


RETURN IF(chk_not_in = 1 && chk_in = 1, 1, 0)

Here below is the output of measure-

Step-3: Now just add the newly created measure to the Visual level filter as below and you will get your expected set of data in the table visual based on your slicer selection.

Create Calculated table based on slicer selection

1 Answers