Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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.
@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..
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.
@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?
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'
@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
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.
@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.
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |