Forum Discussion
Helper VHow can use the same filter to filter same columns independent of each others selections in PowerBI
Hi All,
I have a table visualization in powerBI with two columns QuaterYear and SalesAmountB. I want to add another column SalesAmountB.
I have two filters (QuaterYear - case 1 and Quater Year Case 2] for the same field 'QuaterYear'.
As per my requirement I need the filters QuaterYear - case 1 and QuaterYear - case 2 to filter these two columns SalesAmountB seperarely.The first filter should filter salesamount B column seperate and second filter should filter SalesamoutB seperate.But when I select the QuaterYear - Case 1 , same selection comes preselected in QuaterYear-Case 2.I understand its logical but is there a way using some calculations or parameters I can handle such scenario?
At moment a selection in first filter Quater-Case1 is preselecting Quater-Case2 filter by default.
Expected Output
I want Quater-Case 1 selection should let me choose column SalesAmountB seperately and Quater-Case2 should let me choose column SalesAmount B seperately though both columns are from same and from same table. Could you please suggest?
My present visualizaion
Input data source excel:
| QuaterYear | Sales amount | Place |
| Q12017 | 1000 | London |
| Q12017 | 1100 | London |
| Q12017 | 1120 | London |
| Q12017 | 900 | Tokyo |
| Q22017 | 2200 | Tokyo |
| Q22017 | 2100 | Tokyo |
| Q22017 | 1000 | Tokyo |
| Q22017 | 500 | Tokyo |
| Q22017 | 1900 | Tokyo |
| Q22017 | 1900 | Tokyo |
| Q22017 | 2000 | Tokyo |
| Q22017 | 4000 | Tokyo |
| Q22017 | 2100 | Tokyo |
| Q32017 | 1100 | Singapore |
| Q32017 | 890 | Singapore |
Hi deb_power123 ,
According to my understanding, you want to use the same field to filter the table twice but do not affect each other, right?
You could create two calculated tables:
ScenarioA = VALUES ( 'Table'[QuaterYear] )ScenarioB = VALUES ( 'Table'[QuaterYear] )And use the following formula to create measures:
MeasureA = IF ( MAX ( 'Table'[QuaterYear] ) IN ALLSELECTED ( ScenarioA[QuaterYear] ), 1, 0 )MeasureB = IF ( MAX ( 'Table'[QuaterYear] ) IN ALLSELECTED ( ScenarioB[QuaterYear] ), 1, 0 )Then apply them to filter pane (set as "is 1") as shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
deb_power123 , The information you have provided is not making the problem clear to me. Can you please explain with an example.
In case you need QOQ , please refer
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0Appreciate your Kudos.
deb_power123OK amitchandak let me explain it more clearly. I have a table named sales with three columns: 1)QuaterYear 2)Place 3)SalesAmount-scenarioA.
I have duplicated the column 'SalesAmount-scenarioA' and named it 'SalesAmount-scenarioB in modelling view of PowerBI.I have added 2 filters for values QuaterYear with name QuaterYearscenarioA and QuaterYearscenarioB in my visualization .
I want my filters to filter these two duplicate columns seperately in the table visualization as if they are two different columns and they arent related to each other.For example: if I filter the first column 'SalesAmount-scenarioA' then the values of SalesAmount-scenarioB'should not change.Rightnow if I select any value in first filter the second filter gets preselected and both the column values gets changed. Though I understand why it happens but my reuirement is to filter both these columns independently. In other words like implementing filter columns in table visualization, since powerBI doesnt have one so I am trying to workaround.
This will help me to compare the values through filter for different Quateryear for different scenarios.
How can I handle this case , is there any Calculations or measures you could provide me which I can try? Please help. I have added my input data values in the initial thread for reference .
My modelling tab: where I duplicated the columns
My visualization:
Hi deb_power123 ,
According to my understanding, you want to use the same field to filter the table twice but do not affect each other, right?
You could create two calculated tables:
ScenarioA = VALUES ( 'Table'[QuaterYear] )ScenarioB = VALUES ( 'Table'[QuaterYear] )And use the following formula to create measures:
MeasureA = IF ( MAX ( 'Table'[QuaterYear] ) IN ALLSELECTED ( ScenarioA[QuaterYear] ), 1, 0 )MeasureB = IF ( MAX ( 'Table'[QuaterYear] ) IN ALLSELECTED ( ScenarioB[QuaterYear] ), 1, 0 )Then apply them to filter pane (set as "is 1") as shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- deb_power123
Thankyou for the approach, I did it in a different way by using switch case.But this worked like charm.Cheers
