Forum Discussion
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.
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.