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

I'm not really sure how to compare two columns from the same table in DAX. Assigning a table to a variable in DAX does not allow me to access the column in the variable.

 

Any help would be appreicated.

  • 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

4 Replies

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

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        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