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 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

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])
)

6 Replies

  • tamiribas's avatar
    tamiribas
    Resolver I
    2 years ago

    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.

     

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tamiribas 

     

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

     

    Regards,

    Nono Chen

    • tamiribas's avatar
      tamiribas
      Resolver I
      2 years ago

      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

    • tamiribas's avatar
      tamiribas
      Resolver I
      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 

         

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

         

        Regards,

        Nono Chen