Using a disconnected table to achieve partial visual interaction

[tamiribas]

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 legends, and the YearMonth on the X-Axis.

In the Matrix, the categories are in the rows, and the YearMonth on the Columns.

When selecting a data point from the bar chart, we need the table to display the results for the selected category but keep the columns of all the months that are in the selected Year Slicer. 

We tried the Disconnected dimDate approach:

Please see attached PBIX. 

Test Dates with disconnected tables 

we need help with the measure we show in the Matrix. This is the code so far (which ignores the year from the slicer and the corresponding Month in the column):

SalesAmountInMatrix2 = 
CALCULATE(
    [SalesAmount],
        TREATAS(
        VALUES('dimDate'[YYYY/MM]),
        'dimDateDisconnected'[YYYY/MM]
    )
)

Any help would be appreciated,

Thank you,

Tamir

[v-nuoc-msft]

Hi @tamiribas 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[tamiribas · Accepted solution]

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
_CalcSales

The measure in the Matrix:

SalesAmountInMatrix = 
CALCULATE(
    [SalesAmount],
    TREATAS(
        VALUES(dimDateDisconnected[YYYY/MM]),
        factSales[YYYY/MM]
        ),
        ALL(dimDate[YYYY/MM])
)

[v-nuoc-msft]

Hi @tamiribas 

Thank you very much for your sharing, it will be of great help to others!

Regards,

Nono Chen

[tamiribas]

Hi Nono, changing to ALLSELECTED('dimDate'[YYYY/MM]) did not help.

I managed though to solve it by adjusting both the measure in the bar chart.

I will upload my solution.

Thank you,

Tamir

[v-nuoc-msft]

Hi @tamiribas 

It looks like you're trying to synchronize the bar chart selection with the matrix while leaving the YearMonth column unchanged according to the Year Slicer.

Try adjusting the measure to comply with the Year Slicer and show the correct month in the matrix.

SalesAmountInMatrix2 = 
CALCULATE(
    [SalesAmount],
    TREATAS(
        VALUES('dimDateDisconnected'[YYYY/MM]),
        'dimDate'[YYYY/MM]
    ),
    ALLSELECTED('dimDate'[YYYY/MM])
)

This measure uses to ensure that the YearMonth context from the slicer is respected, and to map the selected YearMonth values from the disconnected table to the main date table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamiribas]

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.