dynamically changing the column display based on slicer selection..

[Sam09]

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.

[v-yulgu-msft]

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

[Sam09]

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

[AkhilAshok]

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] )

[Sam09]

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

[AkhilAshok]

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]

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

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]

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

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'

[Sam09]

@AkhilAshok - Awesome, now the table is created with values in it (Prod2016,Prod2017,Prod2018), but how to do the below logic part, the measure(Values in previous Year ) created by v-yulgu-msft in the suggested answers above..:

SELECTEDVALUE ( SlicerTable[model] ) - 1

[AkhilAshok]

After giving some thought into this, you could do it following way:

1. The SQL for your Model slicer table should be as below:

SELECT 'Prod2016' AS model, 2016 AS year
UNION ALL 
SELECT 'Prod2017', 2017
UNION ALL 
SELECT 'Prod2018', 2018

2. Create the following Measures:

Record # = COUNTROWS(ProdData)

Count Selected Year = 
VAR selectedModel =
    SELECTEDVALUE ( 'Model'[model] )
RETURN
    CALCULATE ( [Record #], ProdData[model] = selectedModel )

Count Previous Year = 
VAR PrevYear =
    SELECTEDVALUE ( 'Model'[year] ) - 1
VAR PrevYearModelTbl =
    FILTER ( ALL ( 'Model' ), 'Model'[year] = PrevYear )
VAR PrevYearModel =
    MAXX ( PrevYearModelTbl, 'Model'[model] )
RETURN
    CALCULATE ( [Record #], ProdData[model] = PrevYearModel )

Let me know if it works.

[Sam09]

@AkhilAshok - Thanks for the inputs. I see two more issues with the logic you provided.

When user selects Prod2018 in the slicer, the table is only showing the values in one column (Count SelectedYear) instead it has to show values for 2017 in (Count PreviousYear column) and 2018(Count SelectedYear column). Similarly when user selects Prod2017 from slicer it has to show 2017 and 2016 year values in the columns shown in the table.

Please see the picture below, it is only showing the values in CountSelectedYear and not displaying the previous year values in the countPreviousYear column.

 

The other issue is it is showing the count individually for the same products as shown in the above picture(Laptop,Mobile). I want to summarize and show the count for each product as shown in the below image.

   

The measure i have created to calculate the column "count2018" is as below:

count2018 = CALCULATE(
 COUNTAX(
        FILTER ( 'ProdData', ('ProdData'[Level] = "A1"  || 'ProdData'[Level] = "A2" || 'ProdData'[Level] = "A3") && (ProdData[Model] = "2018")),
        'ProdData'[Level]   
))

Any inputs are much helpful.

[AkhilAshok]

To me it looks like you have a relationship between the new Model table and ProdData table. Make sure you remove that relationship. Below is the output I got for the dataset you shared and the measures I wrote above: