cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gianmarco
Advocate II
Advocate II

DAX formula

Dear All,

I have the following two tables:

 

TABLE1

CompanYName1Amount€
ALPHA LLC5.000
ACME INC1.000
SOYLENT GREEN CORP3.000
SLUDO LLC4.000
XXX A SEXY HORROR COMPANY6.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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

v-yangliu-msft
Community Support
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:

vyangliumsft_0-1669182777454.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
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:

vyangliumsft_0-1669182777454.png

 

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

Greg_Deckler
Super User
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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 😞

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.