Multiple slicer into one slicer in visual

[Saxon10]

I have 4 different filter for same id and result. I am currently I am currently using 4 different slicer to see result.

If it possible to build 4 different filter in one slicer (ignore the blanks value) instead using 4 different slicer.

If I select the Fillter1 then it will showing relevant(result1) results only.

I am looking for both measure and calculated option.

Data:

Id	Result	Fillter1	Fillter2	Fillter3	Fillter4
123	Win	OK	NO	OK	OK
124	loss	OK	NO	OK	OK
125	tie	OK	NO	OK	OK
126	win	OK	NO	OK	OK
127	win	OK	NO	OK	OK
128	win	OK	OK	NO	OK
129	loss	OK	OK	NO	OK
130	tie	OK	OK	NO	OK
131	tie	OK	OK	NO	OK
132	win	OK	OK	NO	OK
133	win	OK	OK	OK	NO
134	loss	OK	OK	OK	NO
135	loss	OK	OK	OK	NO
136	tie	OK	OK	OK	NO
137	win	NO	OK	OK	NO
138	loss	NO	OK	OK	OK
139	tie	NO	OK	OK	OK
140	win	NO	OK	OK	OK
141	win	NO	OK	OK	OK
142	win	NO	OK	OK	OK

Look like slicer

Fillters

Fillter1

Fillter2

Fillter3

Fillter4

[MFelix]

Hi @Saxon10 ,

Believe that what you are looking for is for the hierarchy in the slicer.

If you pull all your filter columns to the slicer you will get an Hierarchy and then you can filter at will:

If you select OK on the first level then you have OK and NO.

[Saxon10]

@MFelix,

Thanks for your reply and help. Similar to the hierarchy but not both of them in same slicer. (Filters and Filters status its so painful to filter the data)

All filters in one slicer (Filters 1 to 4) and all filters status in one slicer(Ok, No) and crate a measure to the table based on the both slicer.

This is I needed then I can apply the filter in both slicer then it will give a exact filter data. 

[MFelix]

Hi @Saxon10,

So you want to have a slicer to select what column filter by and a other with the options of the slicer?

So you select filter 3 and get the options for filter 3 column and the calculations base on that selection? 

[Saxon10]

@MFelix ,

Yes, that's right.

Example If select Filter1 and selected Filter status is "Ok" then It will show only filter1 data only.

[Saxon10]

@MFelix ,

Example-  I created two tables and I don't know how to link both slicer to the table. Can you please advise...

Here is the file for your reference https://www.dropbox.com/s/ruquw1tv935kp0x/PBI%20fillter.pbix?dl=0

[AlexisOlson]

You can use these new tables in a switching measure:

CountID = 
VAR SelectedFilter = SELECTEDVALUE ( 'FILTERS'[FILTERS] )
VAR SelectedStatus = SELECTEDVALUE ( 'STATUS'[FILTER STATUS] )
RETURN
    SWITCH (
        SelectedFilter,
        "Fillter1", CALCULATE ( COUNT ( 'TABLE'[Id] ), 'TABLE'[Fillter1] = SelectedStatus ),
        "Fillter2", CALCULATE ( COUNT ( 'TABLE'[Id] ), 'TABLE'[Fillter2] = SelectedStatus ),
        "Fillter3", CALCULATE ( COUNT ( 'TABLE'[Id] ), 'TABLE'[Fillter3] = SelectedStatus ),
        "Fillter4", CALCULATE ( COUNT ( 'TABLE'[Id] ), 'TABLE'[Fillter4] = SelectedStatus )
    )

[Saxon10]

@AlexisOlson,

Thank you so much for your reply and help. 

Could you please able to attach the file because I got the error message so may be file will help to understand the logic. 

[AlexisOlson]

All I did was duplicate the bar chart and swap the implicit count of ID with the explicit measure I defined on the duplicate but here's the file.

[Saxon10]

@AlexisOlson ,

Thank you so much for sharing the PBI file and it's working fine. 

I would like to get the same result but other way round, status(Filters1,2,3,4) is row level(Axis) and Result is Y level and count in value.
I created calculated table (Pivot and unpivot table) in order to see multiple legends in same columns/row and I don't know how can I create a releationship from calculated table (Pivot and unpivot table) to data table. I try to use your logic but it's not working.

If I select all the filters and filters status is "Ok" then it will show count of filters status (Criteria range is "All filters" and filters status is "Ok" and if I select individually or multiple then it will show the count of status depends on the selections).

Can you please advise how can I achieve the results.

PBI attached here https://www.dropbox.com/s/7liaje1bq304wp2/Drill%20through%20issue.pbix?dl=0

[AlexisOlson]

Unpivoting does make life easier. You don't need fancy measures.

See attached.

[Saxon10]

@AlexisOlson ,

Thanks for your reply again. 

I am unable to unpivoted the data table because the Result and Filters columns not part of my original data (only id is original data). The Result and Filters columns came from 4 different tables ( I used DAX New calculate column option to bring it those values into data table).

Can you please advise is there anyway I can achieve the desired results without unpivot the data table.
 

[AlexisOlson]

I've given you both pivoted and unpivoted solutions. I don't know what more you're looking for.

[Saxon10]

@AlexisOlson ,

Sorry some reason the releationship not automaically detectded at my end.

I take unpivoted solution from your file and If I select the filters then visual automatically changed depends on my filter selection but the count not changed even if I selected "No" or "ok" it will give the same results only filters are changed. I understand need to change DAX formula in dimresult table but how can get the count for "ok" and "no" for same time against the result? I want to see the count result if select the "ok" or "no".

Example:

Filter 1 win is -6, Loss-4  , Tie -4 for status is "ok" and  "No"- win is -4, Loss -1, Tie-1 but the current status giving overall count 10,5,5.

Attached the PBI for your reference.

https://www.dropbox.com/s/xqm639a3s8a1taj/Drill%20through%20issue-AO.pbix?dl=0

[AlexisOlson · Accepted solution]

You can't get the Status slicer to work the way you have it. You're unpivoting dimResults, which doesn't distinguish between NO and OK.

You need to unpivot the original table, which can be done in DAX exactly the same way you unpivoted dimResults:

UNPIVOT =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Id", Table1[Result],
        "Result", Table1[Result],
        "Filter", "Filter1",
        "Status", Table1[Fillter1]
    ),
    SELECTCOLUMNS (
        Table1,
        "Id", Table1[Result],
        "Result", Table1[Result],
        "Filter", "Filter2",
        "Status", Table1[Fillter2]
    ),
    SELECTCOLUMNS (
        Table1,
        "Id", Table1[Result],
        "Result", Table1[Result],
        "Filter", "Filter3",
        "Status", Table1[Fillter3]
    ),
    SELECTCOLUMNS (
        Table1,
        "Id", Table1[Result],
        "Result", Table1[Result],
        "Filter", "Filter4",
        "Status", Table1[Fillter4]
    )
)

[Saxon10]

@AlexisOlson ,
Thank you so much for your help and advise. Perfect solutions.

[CNENFRNL]

Unpivot the dataset, if possible to apply an intergrated filter.

[Saxon10]

Thanks for your reply and help.

I am unable unpivot because the filter column not part of the raw data. The filter column came from DAX so unable to unpivot. 

Is there any alternative way so I van achieve the results.

[amitchandak]

@Saxon10 , if you need one slicer and all 4 can filter on one value, then you can create a common table, join with 4 columns. One active join, other inactive and activate them using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

else you need 4 slicers

[Saxon10]

Thanks for your reply and its looks like more complex so can you please help.