Forum Discussion

cris1196's avatar
cris1196
Helper I
3 years ago
Solved

Replicate this SQL query in DAX

Hello everyone. I wanted to know if there was a way to replicate, in mesasures, the following SQL queries in DAX, so that it looks as similar as possible to this: Measure = var variable1 = select...
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi cris1196 

    The following shall return unique count of customers. 

    Common Customers =
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[first_order] = "True",
        'Table'[YearMonth] = 202201
    )
VAR T2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[Day] = 1,
        'Table'[YearMonth] = 202201
    )
RETURN
    COUNTROWS ( INTERSECT ( T2, T1 ) )
    New Customers =
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[first_order] = "True",
        'Table'[YearMonth] = 202201
    )
VAR T2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[Day] = 1,
        'Table'[YearMonth] = 202201
    )
RETURN
    COUNTROWS ( EXCEPT ( T2, T1 ) )

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi cris1196 

The following shall return unique count of customers. 

Common Customers =
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[first_order] = "True",
        'Table'[YearMonth] = 202201
    )
VAR T2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[Day] = 1,
        'Table'[YearMonth] = 202201
    )
RETURN
    COUNTROWS ( INTERSECT ( T2, T1 ) )
New Customers =
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[first_order] = "True",
        'Table'[YearMonth] = 202201
    )
VAR T2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[Day] = 1,
        'Table'[YearMonth] = 202201
    )
RETURN
    COUNTROWS ( EXCEPT ( T2, T1 ) )

 

cris1196's avatar
cris1196
Helper I
3 years ago

It worked, thanks! One last question, is there any way to "automate" this? That is, for example, I have twelve 7 yearmonth values (from 202101 to 202107). I know that if I weren't using a calculate, I could create a variable that is selectedvalue(T1[YearMonth]) and then use it as a filter, but in a "calculatedtable" it wouldn't work and I don't know if there is a way to emulate that.

 

So at least, if I manage to get the year column to recognize me, I only have to create 31 variables (one per day), instead of 31 variables for each year/month/yearmonth