Forum Discussion

vega's avatar
vega
Icon for Resolver III rankResolver 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

vega's avatar
vega
Icon for Resolver III rankResolver 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Resolver III rankResolver 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.