Forum Discussion
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?
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: