cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam09 Frequent Visitor
Frequent Visitor

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/t... 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 13
AkhilAshok Established Member
Established Member

Re: dynamically changing the column display based on slicer selection..

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] )
Community Support Team
Community Support Team

Re: dynamically changing the column display based on slicer selection..

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sam09 Frequent Visitor
Frequent Visitor

Re: dynamically changing the column display based on slicer selection..

@AkhilAshok - The results are not displayed correctly when selected 2017. Any inputs?

AkhilAshok Established Member
Established Member

Re: dynamically changing the column display based on slicer selection..

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.

Sam09 Frequent Visitor
Frequent Visitor

Re: dynamically changing the column display based on slicer selection..

@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.

Sam09 Frequent Visitor
Frequent Visitor

Re: dynamically changing the column display based on slicer selection..

@AkhilAshok - Yes, solution from v-yulgu-msft works but the issue is i cannot able to create a new table in my file as i'm fetching the data using directQuery instead of import..

AkhilAshok Established Member
Established Member

Re: dynamically changing the column display based on slicer selection..

Is PBI Report DirectQuery from SQL Server or SSAS? If from SQL Server, you can now enable the preview feature Composite Models which helps you to combine, data import with DirectQuery.

 

 

Sam09 Frequent Visitor
Frequent Visitor

Re: dynamically changing the column display based on slicer selection..

@AkhilAshok - Thanks for the inputs. I'm using older version of PowerBI(Mar 2018) and it doesn't have "Preview Features" option to select Composite Models option. Is there any alternate way to do it using Measures or DAX formula?

AkhilAshok Established Member
Established Member

Re: dynamically changing the column display based on slicer selection..

In that case, assuming your source is SQL Server, you can add a new table with the below Custom SQL (in the GetData -> SQL Databae dialogue, after entering ur Server and DB, Click Advanced option, and enter the below SQL), and keep it as diconnected table:

 

SELECT 'Prod2016' AS model
UNION ALL 
SELECT 'Prod2017'
UNION ALL 
SELECT 'Prod2018'