Table and relationships

[samueldegrace]

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 · Accepted solution]

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 · Accepted solution]

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

[richbenmintz · Accepted solution]

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