Slicers & Data Display

[SuryaDave_Syd]

Dear All,

I am very new to Power BI & need help - Please see my problem below & refer to Power BI file attached ( May 2021 version ). Power BI file has 3 tabs.     PBI_File_with_demoDATA 

Context:

I have report where user first land on the welcome page & there they select a city. That slicer will filter so many KPIs spread across different pages.

1st Constraint

As per the main condition or constraint for the report, user can select "one" member from the slicer only. So, for example, user can select "City_A" only or user can select "STATE_LEVEL" only ....but ...user can not select 2 or more items from the slicer - i.e. "City_A" & "STATE_LEVEL"

2nd Constraint

Slicer has to be on the welcome page only and not on other pages to avoid confusion to user and also to prohibit user from using slicers to change options. So, I will be using "Sync slicers" functionality available in Power BI to sync slicer across all the pages

Problem:

I want to create a chart, where chart is filtered with city that user has selected from slicer but it also displays state-level result side by side for comparison. However, this is not happening .

What I have tried so far.....

So far I have tried creating calculated column using "selectedvalue" function, tried creating measure using same function but noting is working.

[MFelix · Accepted solution]

Hi @SuryaDave_Syd ,

Looking at the model you have you need to create a disconnect table that will allow you to do the filtering of your data and then a measure to make it to work.

Before giving the step by step, let me try and explain what is happening, when you filter to a specific city you data will only show that city value, because since everything is in the same table you are not abble to tell the data to show something that is not consider.

If you filter on City_A this is what you get

So the calculations cannot present anything else since when you search for State Level that value is not present on the current filtering selection.

If you had a table do the filter you can then change the final result because you can influence how the city will be filtered out.

• Create a new table with the BU

• Add the following measure to your model

Average Value = IF(
			SELECTEDVALUE(Sheet1[Business_Unit]) = "STATE_LEVEL" || SELECTEDVALUE(Sheet1[Business_Unit]) = SELECTEDVALUE(BU[Business_Unit]),
			AVERAGE(Sheet1[Values])
		)

• Use the new table on your slicers and the measure on the values
  
  
  

Has you can see now chart 2 is equal to chart 1 where you forced the values.

PBIX file attach.

[MFelix]

Hi @SuryaDave_Syd ,

Have you tried turning on the single select option on the slicer? For the second part you need to copy the slicer for the other pages and sync them, and then on the pages where you don't want the user to change them you need ti hide them.

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-deskt...

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-deskt...

[SuryaDave_Syd]

Thanks for your response Miguel. However, that is not what my problem is.  My slicers are going to be single select and it will be synced across pages like I mentioned in my post. 

The problem is " when slicers are synced  it will obviously filter the data / chart.  But what I want is, chart should display whatever the selection done in single select slicer and in addition to that it should display other item as well for comparison.  ( as business requirment is slicer has to be single select - need to come up with other solution to acheive it )  please refer to my PBI file where I have given detailed explanation of my problem. 

[MFelix · Accepted solution]

Hi @SuryaDave_Syd ,

Looking at the model you have you need to create a disconnect table that will allow you to do the filtering of your data and then a measure to make it to work.

Before giving the step by step, let me try and explain what is happening, when you filter to a specific city you data will only show that city value, because since everything is in the same table you are not abble to tell the data to show something that is not consider.

If you filter on City_A this is what you get

So the calculations cannot present anything else since when you search for State Level that value is not present on the current filtering selection.

If you had a table do the filter you can then change the final result because you can influence how the city will be filtered out.

• Create a new table with the BU

• Add the following measure to your model

Average Value = IF(
			SELECTEDVALUE(Sheet1[Business_Unit]) = "STATE_LEVEL" || SELECTEDVALUE(Sheet1[Business_Unit]) = SELECTEDVALUE(BU[Business_Unit]),
			AVERAGE(Sheet1[Values])
		)

• Use the new table on your slicers and the measure on the values
  
  
  

Has you can see now chart 2 is equal to chart 1 where you forced the values.

PBIX file attach.

[SuryaDave_Syd · Accepted solution]

Hi Miguel, this is perfect & doing exactly what I was aiming to achieve. Also appreciate explanation & logic so that I learn to fish. 

You are champion !!! Thanks very much & more power to you !!