Method to show different filtered results from the same table on the same visualization

[id013]

Hi,

So I suspect I'm out of luck for this issue but I figured I'd give it a go. So I have a visualization I'm creating from a tabular model (via analysis services) using direct query mode. So that means I don't have access to creating new tables or new columns. 

The data I'm looking at is medical data, let call it patient volumes. Now my data is coming from a single table. That table has 2 fields that I'm using for slicers, one is the referring facility (facility where referral was made) and the treatment facility (facility where treatment was done). My problem is I want to plot patient volume from referral facility and patient volume from treated facility on the same visualization. The problem is as soon as I choose one filter, it already changes the dataset available for the second filter. 

For example say I have 10 patients, 7 have a referring facility of A and 10 have a treatment facility of B. As soon as I select a referring facility of A, it will only show 7 patients that have a treatment facility of B. I originally thought I could unlink the visualization from the facility slicers and then just use DAX queries to filter based on the value of the slicer but because I've unlinked the slicer I can't get the selected value of the slicer. I know there's a selectedvalue function but I can't use it in direct query mode plus I was informed that max/min should get me the same value but that doesn't work since it just gives me the max/min facility values instead of the value I've selected. If I link the slicers to the visualization then the formula works but then I run into the original problem where the slicers are conflicting with each other. 

I'm guessing I need to either plot 2 visualizations, 1 for referring faciltiy and 1 for treatment or conversely see if I can get 2 data tables, one just with patients and their referral facility and one with just patients and their treatment facility but I was hoping that someone might be able to suggest a different way to do it.

[v-yuta-msft]

@id013,

So your issue is one slicer will affect another slicer when values have been selected, right? You can click Format-> Edit interactions. Then in the right-top hand of two slicer visuals, click "None" button to disable the interaction between two slicers.

 

Community Support Team _ Jimmy Tao

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

[id013]

Hi,

Yeah I thought about that but the problem is i do actually need both filters, so I think adetogni had the right idea in that I either need 2 separate visualizations or 2 separate data tables, since basically I'm looking at 2 different queries. 

I originally tried editting the interactions so the visualization wasn't linked to either slicer and have DAX handle the filtering for me but that won't work since there's a way to set the selected value in a slicer to a variable of some kind and use that in my dax queries instead.

Thanks though

[v-yuta-msft · Accepted solution]

@id013 ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[id013]

---

@v-yuta-msft wrote:

@id013 ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

---

Hi,

Yes I solved my issue but it had to be done on the tabular model level as they just assigned the relationships in the model.

Thanks

[v-yuta-msft]

@id013 ,

It seems like hierarchy slicer may meet your requirement based on your senario. I would suggest you to build a hierarchy columns for the other columns and drag the hierarchy columns in a hierarchy slicer.

    

Reference:https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/297427 

Community Support Team _ Jimmy Tao

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

[Anonymous]

I'll make a guess as I'm not confident with DirectQuery BUT in this mode, AFAIK, each "slicer" selection actually queries the dataset. So one visual->one query. Therefore I don't think it's something you can do. To be honest using PowerBI in DQ *without* having access to the data model - and I mean write mode to create measures and columns, is fairly limited.

But again, that's my partial understanding of the DQ.