DAX - How to use a table result into other expression ?

[gaizcorbe]

Hi there,

     I have a small problem, let's say I have a table "Sales" where I have columns like Date, Customer, Volume (and others that doesn't play in this game ).

For Date,Customer I can have volumes <0, =0, >0

Now I want to count all the (distinct) customers that has more than two rows with volume > 0

Would be something like

SELECT Customer, COUNT(*)
FROM Sales
WHERE Volume > 0
GROUP BY Customer
HAVING COUNT(*) > 2

I already solved this in a way I don't like much

I created a table

AuxTbl = SUMMARIZE(FILTER(Sales, Sales[Volume] > 0), Sales[Customer], "CustomerSales", COUNT(Sales[Volume]))

And then I have created a measure in my Sales table like

MyMeasure  = CALCULATE(COUNT(AuxTbl[Customer]), FILTER(AuxTbl,AuxTbl[CustomerSales] > 2))

But this has two things I don't like
1) The extra table (AuxTbl)
2) Slicers doesn't work over AuxTbl so when I apply slicers like Date from Sales, my card with "MyMeasure" doesn't change

I tryed something like this

MyMeasure = 
var auxTbl = SUMMARIZE(FILTER(Sales, Sales[Volume] > 0), Sales[Customer], "CustomerSales", COUNT(Sales[Volume]))
var calc = CALCULATE( ... )
return calc

but I reach a dead end because though SUMMARIZE returns a table, I can't use auxTbl in my CALCULATE sentence

So my auxTbl is like a "temp table" but how do I work with in my next sentence ?
am I in the right way ?

I guess there are a thousand ways to do this and I would like to know this other ways, but I'm specially interested into understand how to manipulate "temp tables" returned by other DAX functions like SUMMARIZE or FILTER etc....

Thanks in advance for your help and excuse the length of the post but needed to provide the whole scenario to you

Regards,
Gabriel

[Zubair_Muhammad]

Hi @gaizcorbe 

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Sales[Customer] ),
        CALCULATE ( COUNTROWS ( Sales ), Sales[Volume] > 0 ) > 2
    )
)

[gaizcorbe]

Hi Zubair, I tried this

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Sales[Customer] ),
        CALCULATE ( COUNTROWS ( Sales ), Sales[Volume] > 0 ) > 2
    )
)

but for some reason didn't work
May be I can send a TEST.PBIX for you to see, let me know
Best regards
Gabriel 

[Zubair_Muhammad]

Hi Gabriel
You can send me the test file. I will take a look later tonight when i get my hands on laptop.

[gaizcorbe]

Hi Zubair,

    I'm leaving the pbix in a drive, let me know if you can't access it

Download PBIX from here

Thanks and best regards

Gabriel

[Zubair_Muhammad · Accepted solution]

@gaizcorbe 

With your formula, you might be able to use

MyMeasure =
VAR auxTbl_ =
    SUMMARIZE (
        FILTER ( Sales, Sales[Volume] > 0 ),
        Sales[Customer],
        "CustomerSales", COUNT ( Sales[Volume] )
    )
VAR calc =
    COUNTROWS ( FILTER ( auxTbl_, [CustomerSales] > 2 ) )
RETURN
    calc

or

MyMeasure 1 =
VAR auxTbl_ =
    SUMMARIZE (
        FILTER ( Sales, Sales[Volume] > 0 ),
        Sales[Customer],
        "CustomerSales", COUNT ( Sales[Volume] )
    )
VAR calc =
    CALCULATE (
        DISTINCTCOUNT ( Sales[Customer] ),
        FILTER ( auxTbl_, [CustomerSales] > 2 )
    )
RETURN
    calc

[gaizcorbe]

Hi Zubair,

    I did minor enhancements in the formulas you gave me but where excellent points to start with, look the one is working and being affected by the slicers!!

MyMeasure = 
    var auxTbl_ = SUMMARIZE(FILTER(Sales,Sales[Volume]>0),Sales[Customer],"CustomerTransactions",COUNT(Sales[Volume]))
    var calc = CALCULATE(COUNTROWS(FILTER(auxTbl_, [CustomerTransactions] >= 2)))

RETURN
    calc

I didn't know how to operate with variable tables 🙂 now I have a nice idea and the measure is working very well.
Thanks again for your time and help

Best regards
Gabriel

[gaizcorbe]

Thank you very much Zubair!! 

I've learned something new!!

Amazing solutions 🙂