DAX formula

[gianmarco]

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

[Greg_Deckler · Accepted solution]

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

[v-yangliu-msft · Accepted solution]

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

[Greg_Deckler]

@gianmarco Try:

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

[gianmarco]

Dear @Greg_Deckler 

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

[Greg_Deckler · Accepted solution]

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