Forum Discussion

gianmarco's avatar
gianmarco
Helper IV
3 years ago
Solved

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    gianmarco Try:

    Measure = 
  VAR __Table1 = SELECTCOLUMNS('TABLE1',"__Company",[CompanyYName1])
  VAR __Table2 = SELECTCOLUMNS('TABLE2',"__Company",[CompanyYName1])
  VAR __Result = COUNTROWS(EXCEPT(__Table2, __Table1))
RETURN
  __Result
    • gianmarco's avatar
      gianmarco
      Helper IV
      3 years ago

      Dear Greg_Deckler 

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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