Forum Discussion

samueldegrace's avatar
samueldegrace
New Member
9 years ago
Solved

Table and relationships

Hello, 

 

I'm having a hard time trying to find a solution to a problem. I created a table that looks a like this: 

 

 

 

 

Now, I only want to show rows where [Montant] is higher than [Budget]. The problem is, this data comes from two other tables, like this: 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Montant] comes from CHARTE and [Budget] from BUDGET. I tought of creating a column in the Master table that shows 1 or 0 depending of if I bust the budget or not and filtering on that, since it's this table (Master) that I use for the table in the first picture, but the RELATED function only works in a many-to-one relationship, and I have the oposite. As a result, I can't use a syntax like this:

 

Bust = IF(RELATED(CHARTE[Montant] > RELATED(BUDGET[Budget], 1, 0)

 

I searched alot online, but no luck. Any ideas would be realy appreciated. 

 

Thank you! 

  • richbenmintz's avatar
    richbenmintz
    9 years ago

    You can create a measure that looks to see if the montant > budget

    test measure = if(sum(montant) > sum(budget), true(), false())

     

    you can then add this measure to your visual filters use it to filter the data. 

    or you can set montant to blank() if the budget is greater

    like montant = if(sum(montant) > sum(budget), sum(montant), blank())

    and then filter the visual to only show rows with non blank Montant values.

     

    Hope that helps,

     

    Richard

  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    maybe you can try the RELATEDTABLE() used in a CALCULATE() like so

    Bust = 
IF(CALCULATE(SUM('CHARTE'[Montant]), RELATEDTABLE('CHARTE')) >
CALCULATE(SUM('BUDGET'[Budget]), RELATEDTABLE('BUDGET')),1,0)

    RELATEDTABLE() is the opposite from RELATED. RELATETABLE pulls from the many side, for this reason it is necessary to provide an aggregate function.

     

    Hope this helps

    Regards

2 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    9 years ago

    You can create a measure that looks to see if the montant > budget

    test measure = if(sum(montant) > sum(budget), true(), false())

     

    you can then add this measure to your visual filters use it to filter the data. 

    or you can set montant to blank() if the budget is greater

    like montant = if(sum(montant) > sum(budget), sum(montant), blank())

    and then filter the visual to only show rows with non blank Montant values.

     

    Hope that helps,

     

    Richard

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    maybe you can try the RELATEDTABLE() used in a CALCULATE() like so

    Bust = 
IF(CALCULATE(SUM('CHARTE'[Montant]), RELATEDTABLE('CHARTE')) >
CALCULATE(SUM('BUDGET'[Budget]), RELATEDTABLE('BUDGET')),1,0)

    RELATEDTABLE() is the opposite from RELATED. RELATETABLE pulls from the many side, for this reason it is necessary to provide an aggregate function.

     

    Hope this helps

    Regards