Replicate this SQL query in DAX

[cris1196]

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 customer from T1 where Filter1 and Filter2
var variable2 = select customer from T1 where Filter2 and Filter3

return
select count(customer) from variable2 where customer in (or not in) (select customer from variable1)

Or

Measure =
select count(customer) from T1
where Filter and Filter and customer in (
select customer from T1 where Filter and YFilter)

So far everything I've tried to do hasn't worked  🙃

Thanks!

[CNENFRNL]

The [first_order] column is redundant.

For fun only, a showcase of powerful Excel worksheet formula,

[tamerj1 · Accepted solution]

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]

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

[tamerj1]

@cris1196 
Would you please elaborate on what are you trying to achieve?

[cris1196]

So if I create an array whose rows are [YearMonth] and columns are [Days] (or I can generate another table with generatedseries(0,12,1) for the months/periods):

that PBI automatically calculates these values, without the need to create several variables.

Just in case, if I didn't explain myself well: if I wanted "number of users with first order = true per day", I could do:

 I hope I have explained myself well, if not, I apologize and honestly I do not want to take up any more of your time: I already created all the variables I needed, this question is just an irrelevant extra curiosity.

Again, many thanks for your help 🙂

[tamerj1]

@cris1196 
I was hopping that you explain what are trying to achieve not how are you trying to achieve it. However there is a chance that I correctly guessed your requirement. It could be much simpler than you might think. Please try

Common Customers =
VAR CurrentMonth =
    SELECTEDVALUE ( 'Table'[YearMonth] )
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Cutomer] ),
        'Table'[first_order] = "True",
        'Table'[YearMonth] = CurrentMonth - 1,
        ALL ( 'Table'[Day] )
    )
VAR T2 =
    VALUES ( 'Table'[Cutomer] )
RETURN
    COUNTROWS ( INTERSECT ( T2, T1 ) )

[cris1196]

I tried to put examples so I tried to explain it better. Thank you very much for your help