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.

  • 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

    • vega's avatar
      vega
      Resolver III
      8 years ago

      I went ahead and marked the above as the solution. I'm sure it's not ideal, as it requires you to make a second table, which requires more space. I was able to solve the problem a different way by using calculated columns. In case anyone is interested the calculated column is as follows:

      =
NOT(
	ISEMPTY(
		FILTER(Query1, [AccountNumber] = EARLIER([AccountNumber]) && [AccountDescription] <> EARLIER([AccountDescription]))
	)
)

      This will create a calculated column where the value is TRUE when the AccountNumber has an identical AccountNumber, but with a different AccountDescription and FALSE if there are no other AccountNumbers which are the same with different AccountDescriptions. I perfer this method as it adds one column. I could not figue out how to get this done as a Measure.

      P.S.
      Thank you for all of your help I greatly, greatly appreciate it.