Forum Discussion

primolee's avatar
primolee
Icon for Helper V rankHelper V
4 years ago

Select slicer and affect the other table without using relationship

Hello there,

 

I created a very simple BI as an example of my question.

 

I have two tables, monthly sales and expense, and I have two slicers of sales year and month.

 

Is there a way to select the sales year and month, and the expense table will also show the corresponding year and month?

 

I can make a common key to relate them, but in my real-life case due to nested relationship these 2 tables cannot be related.

 

Sample file is here: https://drive.google.com/file/d/1CA8ijh2xxDra1rpXYngkWk2KhXDPFGY-/view?usp=sharing 

 

Thank you.

 

Best regards,

David

Need Help

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You need to create a Date table which you link to both your other tables, then use slicers based on the date table. That will filter both.

    • primolee's avatar
      primolee
      Icon for Helper V rankHelper V
      4 years ago

      As mentioned in my post, my actual case has too many nested relations already, i'm not able to join them even if I create a new year month table.

       

      just wondering if there is any other way such as using selectedvalue.  Thanks.

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        It's possible with SELECTEDVALUE but using a date dimension table is very likely a better option.

         

        I don't understand why "too many nested relations" prevent this option. Can you show what your model diagram looks like (or an analogous simplified example)?

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I don't recommend this approach but you can pass filters from the sales table to the expense table like this:

    SumExpense = 
CALCULATE (
    SUM ( 'Monthly Expense'[Expense] ),
    KEEPFILTERS ( TREATAS ( VALUES ( 'Monthly Sales'[Month] ), 'Monthly Expense'[Month] ) ),
    KEEPFILTERS ( TREATAS ( VALUES ( 'Monthly Sales'[Year] ), 'Monthly Expense'[Year] ) )
)

    or similarly but with slightly different syntax

    SumExpense2 =
CALCULATE (
    SUM ( 'Monthly Expense'[Expense] ),
    FILTER (
        VALUES ( 'Monthly Expense'[Month] ),
        'Monthly Expense'[Month] IN VALUES ( 'Monthly Sales'[Month] )
    ),
    FILTER (
        VALUES ( 'Monthly Expense'[Year] ),
        'Monthly Expense'[Year] IN VALUES ( 'Monthly Sales'[Year] )
    )
)

     

    See this article for more information on different ways of propagating filters:
    https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/