Forum Discussion
Solved
Using a disconnected table to achieve partial visual interaction
Hi, We have a Bar Chart, a Matrix and a Year Slicer on the same page. Both Bar Chart and Matrix are showing [SalesAmount] by Category and YearMonths. In the bar chart, the categories are in the...
Here is a link to a PBIS with the correct functionality.
https://drive.google.com/file/d/1Gh7yBEZcwZJfbQKwgEr5N4DBy7Re3_md/view?usp=drive_link
The date slicer is taken from the disconnected dim table.
The date in the Barchart is taken from the dim date table.
The date in the Matrix is taken from the disconnected dim table.
I adjusted the measure in the Barchart so that :
SalesAmountInBarChart = VAR _MinSelected = MIN('dimDateDisconnected'[YearMonth]) VAR _MaxSelected = MAX('dimDateDisconnected'[YearMonth]) VAR _CalcSales = IF ( SELECTEDVALUE('dimDate'[YearMonth])>=_MinSelected && SELECTEDVALUE('dimDate'[YearMonth])<=_MaxSelected, [SalesAmount],BLANK() ) RETURN _CalcSalesThe measure in the Matrix:
SalesAmountInMatrix = CALCULATE( [SalesAmount], TREATAS( VALUES(dimDateDisconnected[YYYY/MM]), factSales[YYYY/MM] ), ALL(dimDate[YYYY/MM]) )
I believe I almost solved it...
I am still not 100% through...as it is not working when I add quarters and months to the slicer.
Here is my current solution:
SalesAmountInMatrix =
CALCULATE(
[SalesAmount],
TREATAS(
VALUES(dimDateDisconnected[YYYY/MM]),
factSales[YYYY/MM]
),
ALL(dimDate[YYYY/MM])
)
[SalesAmount] -
Is evaluated within the current filter context (Selected category from the bar chart, a year from the slicer)
The TREATAS propagates the YYYY/MM filter from the disconnected dimDate to the fact table.
TREATAS(
VALUES(dimDateDisconnected[YYYY/MM]),
factSales[YYYY/MM]
)
filters the yyyy/mm in the fact table to match the month in the columns. This way, the measure is computed for each month.
ALL(dimDate[YYYY/MM])
Allow all the months in the selected year to be displayed and not filtered as in the chart.