Forum Discussion

vega's avatar
vega
Resolver III
8 years ago
Solved

SQL to DAX

I'm new to DAX and would like to do the following in DAX. Select a1.accountNumber From a1 Account, a2 Account Where a1.AccountNumber == a2.AccountNumber AND a1.AccountDescription != a2.AccountDe...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi vega,

     

    If I understand you correctly, you should be able to simply use the formula below to create new table to get number of AccountDescriptions for each AccountNumber.

    Table = 
SUMMARIZE (
    Account,
    Account[AccountNumber],
    "Number Of Description", DISTINCTCOUNT ( Account[AccountDescription] )
)

     

    Then you can apply a filter to show only accounts which have the same AccountNumber, but different AccountDescriptions on the report. :smileyhappy:

     

     

    Regards

MFelix's avatar
MFelix
Super User
8 years ago

Hi vega,

 

Not really sure what you are aiming to do, do you have two tables in your model and pretend to have it a comparision between the two tables where the account number is equal but the description is diferent?

 

I created two table with two columns: account and Description

 

 

Then create a column in table 1 and put it on your visual and filter out the blanks values:

 

Comparision =
IF (
    LOOKUPVALUE ( Table2[Description], Table2[Account], Table1[Account] )
        = Table1[Description],
    "",
    "Different Account Name"
)

 

Result is below:

 

 

Not sure if this is what you are looking for.

 

regards,

MFelix

 

vega's avatar
vega
Resolver III
8 years ago

I have one table, Accounts. And I want to return all the account which have the same AccountNumber, but different AccountDescriptions. Is it possbile to do this comparison on only one table? I know in SQL I can create an alias for the same table and then do the comparison that way.