Forum Discussion
dynamically changing the column display based on slicer selection..
Is it possible to dynamically change the columns which i'm showing in the table visualization based on the option selected in the slicer.
I have the below table:
MobID Pname level Model 100 Mobile A1 Prod2018 109 PC A1 Prod2018 188 Mobile A2 Prod2018 190 Laptop A2 Prod2016 200 TV A3 Prod2016 333 PC A1 Prod2017 377 Laptop A2 Prod2018 399 Mobile A3 Prod2017 400 Mobile A3 Prod2016 403 Laptop A1 Prod2017 404 Laptop A3 Prod2018 405 Laptop A3 Prod2017 406 TV A1 Prod2017 407 TV A1 Prod2017
Created the below measures.
count2016 = CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A1" || 'ProdData'[Level] = "A2" || 'ProdData'[Level] = "A3" && ProdData[Model] = "Prod2016"), 'ProdData'[Level]
))
count2017= CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A1" || 'ProdData'[Level] = "A2" || 'ProdData'[Level] = "A3" && ProdData[Model] = "Prod2017"), 'ProdData'[Level]
))
count2018 = CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A1" || 'ProdData'[Level] = "A2" || 'ProdData'[Level] = "A3" && ProdData[Model] = "Prod2018"), 'ProdData'[Level]
))
Below are the steps i followed.
1)Created Chiclet slicer with field Model and kept the filter to show only Prod2017 and Prod2018 options on the slicer.
2)Created table visualization with fields Pname and showing the measures count2017 and count2016 as shown in above picture.
I want to dynamically change the columns shown in the table visualtization(count2017,count2016) when user change the option in chiclet slicer. When user selects 2017 from slicer, i want to show the columns pname,count2017,count2016 and when user selects 2018 from slicer, the columns should be pname,count2017,count2018.
Another issue i'm facing is based on the slicer selection, the count is also changing which should not happen(might be because i have not selected 2016 to show in the slicer).
I was looking this https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/ta-p/162635 thread, but my requirement is different and when tried to show two columns on selection of slicer it is not giving the expected result. Need help..
--EDITED--
I have edited the Model column in the table shown above, it actually holds values as Prod2016,Prod2017,Prod2018..
1) When showing in the slicer can i show as Year2017,Year2018 and when user selects Year2017 from slicer, table visualization should show the pname,count2016,count2017 and when user selects Year2018 from slicer table visualization should show pname,count2017,count2018.
13 Replies
Hi Sam09,
First, please set the data type of [Model] to whole number in your data table.
Second, as mentioned in above link, you should create an extra table (suppose it's 'SlicerTable') to list all slicer selections. In this scenario, selecton should be 2016, 2017, 2018.
Third, make sure this new created table is unrelated to data table.
Create measures and add them to visual.
Values in current Year = CALCULATE ( COUNTAX ( FILTER ( 'ProdData', 'ProdData'[Level] = "A1" || 'ProdData'[Level] = "A2" || 'ProdData'[Level] = "A3" && ProdData[Model] = SELECTEDVALUE ( SlicerTable[Year] ) ), 'ProdData'[Level] ) )
Values in previous Year =
CALCULATE (
COUNTAX (
FILTER (
'ProdData',
'ProdData'[Level] = "A1"
|| 'ProdData'[Level] = "A2"
|| 'ProdData'[Level] = "A3"
&& ProdData[Model]
= SELECTEDVALUE ( SlicerTable[Year] ) - 1
),
'ProdData'[Level]
)
)Best regards,
Yuliana Gu
v-yulgu-msft - Thanks for the detailed explanation. It works as expected, but issue is i cannot create a new table to hold slicer values, as i'm getting the data using DirectQuery(should not change to import) mode which doesn't allow to create a new table. Can i use the existing table column(Model) in the slicer, but issue here is the Model has 2016,2017 and 2018 as values but in the slicer i just need to show 2017 and 2018 and change the column values accordingly. Please see my edited section in the post above, i made small change to the column Model.Before the values in the Model column were 2016,2017,2018, i modified to Prod2016,Prod2017,Prod2018.
You can create 2 generic measures like this and use:
Count Previous Year = SWITCH ( SELECTEDVALUE ( ProdData[Model] ), "2017", [count2016], [count2017] ) Count Selected Year = SWITCH ( SELECTEDVALUE ( ProdData[Model] ), "2017", [count2017], [count2018] )
AkhilAshok - The results are not displayed correctly when selected 2017. Any inputs?
Have u tried the solution from v-yulgu-msft? That should work fine. My approach may not work, since the filter on year will filter the Pname dimensions. So you have to create a diconnected table with Year values and use that as a slicer.
