Forum Discussion

NoSayMe's avatar
NoSayMe
Regular Visitor
4 years ago
Solved

Data Model - switch relation from inactive to active by filter.

Hi, looking for help with the following ...

I got 2 tables:
Customers:

Spending:


Filter:

 

Filter Measures:

 

Data Model:


Report:

 

I need to switch column filter according to the selected value in my custom filter.

Say I want to see how much money did Raechel spent on different categories (only 1 for this example) by year, but only her own money ( So 5$ her own money on coffee in 2022 ).

 

Other option would be how much of friends money has she spent ( So 9$ of friends money on coffee in 2022 ), doesn't matter which friend.

But I also want to be able to select Monica and see how much of her money has she spent ( So 9$ of her own money on coffee in 2022 ).

Link: Download .pbix

Thanks
Juraj

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi NoSayMe ，

    Sorry I can't access your sample file. According to your description, I create a sample. I add some data in the Spending table like below.

    You want to show the seperate amount differentiate by own paid or friends paid for each customer.

    Here's my solution, modify the measure.

    Own money =
CALCULATE (
    SUM ( 'Spending'[Amount] ),
    'Spending'[UsedOwnMoney] = TRUE,
    USERELATIONSHIP ( 'Spending'[CustomerID], 'Customers'[Customers_ID] )
)

    Friends money =
CALCULATE (
    SUM ( 'Spending'[Amount] ),
    'Spending'[UsedOwnMoney] = FALSE,
    USERELATIONSHIP ( 'Spending'[CustomerID], 'Customers'[Customers_ID] )
)

    Get the result.

    Then if you want to ignore the who paid switch and calculate the sum amount, create a measure.

    Measure =
CALCULATE (
    SUM ( Spending[Amount] ),
    USERELATIONSHIP ( Spending[CustomerID], 'Customers'[Customers_ID] )
)

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Read about using Field Parameters.  They also work with measures and can simplify what you are trying to achieve.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi NoSayMe ，

    Sorry I can't access your sample file. According to your description, I create a sample. I add some data in the Spending table like below.

    You want to show the seperate amount differentiate by own paid or friends paid for each customer.

    Here's my solution, modify the measure.

    Own money =
CALCULATE (
    SUM ( 'Spending'[Amount] ),
    'Spending'[UsedOwnMoney] = TRUE,
    USERELATIONSHIP ( 'Spending'[CustomerID], 'Customers'[Customers_ID] )
)

    Friends money =
CALCULATE (
    SUM ( 'Spending'[Amount] ),
    'Spending'[UsedOwnMoney] = FALSE,
    USERELATIONSHIP ( 'Spending'[CustomerID], 'Customers'[Customers_ID] )
)

    Get the result.

    Then if you want to ignore the who paid switch and calculate the sum amount, create a measure.

    Measure =
CALCULATE (
    SUM ( Spending[Amount] ),
    USERELATIONSHIP ( Spending[CustomerID], 'Customers'[Customers_ID] )
)

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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