How to use IF in an aggregation involving two different tables ?

Bebs — Tue, 13 Sep 2022 09:33:34 GMT

Hello, 🙂

I'm struggling with DAX syntax on something which seems to be a simple if :).

I've two tables in my model : the fact table seems like this :

Company, Result

A, 3

B, - 5

C, 100

D, -30

there is a link on a secondary table :

Company, Group

A, AA

B, AA

C, BB

D, BB

1) I'd like to display a table "Loss by Group"

AA, -5

BB, -30

2) If 1) is Okay, i'd like to display a table like this

total positive, 103

total negative, -35

total , 68

Can you please help me on the DAX measure syntax ?

Best regards

Re: How to use IF in an aggregation involving two different tables ?

amitchandak — Tue, 13 Sep 2022 10:23:46 GMT

Bebs , if the relation is Many to 1 (secondary)

calculate(Sum(Fact[Result]), filter(fact, Fact[Result]< 0) )

for the second done you have a column in fact and use that grouping and just count

if( Fact[Result] >=0 , "Positive", "Negative")