Forum Discussion

tamiribas's avatar
tamiribas
Resolver I
2 years ago
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...
Need Help
  • tamiribas's avatar
    tamiribas
    2 years ago

    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])
)
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.