cancel
Showing results for
Did you mean:

DAX formula

Dear All,

I have the following two tables:

TABLE1

 CompanYName1 Amount€ ALPHA LLC 5.000 ACME INC 1.000 SOYLENT GREEN CORP 3.000 SLUDO LLC 4.000 XXX A SEXY HORROR COMPANY 6.000

TABLE2

 CompanYName2 ALPHA LLC ACME INC SOYLENT GREEN CORP

Those two tables are linked through a relationship.

I have a measure that is: [TABLE1]SUM(Amount€) = 19.000

I would like to create a measure which gives me back 10.000 which is the amount of the companies listed in TABLE2 which are not listed in TABLE1.

Thank you a lot for your time
Best regards

gianmarco

2 ACCEPTED SOLUTIONS
Super User

@gianmarco It should be if you use the same "__Company" column name. That's the reason to use SELECTCOLUMNS. I mocked it up and it seems to work. See attached PBIX below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Community Support

Hi  @gianmarco ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table2=SELECTCOLUMNS('Table2',"1",[CompanYName2])
return
SUMX(FILTER(ALL(Table1),
NOT('Table1'[CompanYName1]) in _table2),[Amount€])

2. Result:

Best Regards,

Liu Yang

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

4 REPLIES 4
Community Support

Hi  @gianmarco ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table2=SELECTCOLUMNS('Table2',"1",[CompanYName2])
return
SUMX(FILTER(ALL(Table1),
NOT('Table1'[CompanYName1]) in _table2),[Amount€])

2. Result:

Best Regards,

Liu Yang

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

Super User

@gianmarco Try:

Measure =
VAR __Table1 = SELECTCOLUMNS('TABLE1',"__Company",[CompanyYName1])
VAR __Table2 = SELECTCOLUMNS('TABLE2',"__Company",[CompanyYName1])
VAR __Result = COUNTROWS(EXCEPT(__Table2, __Table1))
RETURN
__Result

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Dear @Greg_Deckler

it says for every table argument of EXCEPT it must be the same column number which is not 😞

Super User

@gianmarco It should be if you use the same "__Company" column name. That's the reason to use SELECTCOLUMNS. I mocked it up and it seems to work. See attached PBIX below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!