Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Hi @gaizcorbe
Try this MEASURE
Measure = COUNTROWS ( FILTER ( VALUES ( Sales[Customer] ), CALCULATE ( COUNTROWS ( Sales ), Sales[Volume] > 0 ) > 2 ) )
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
Hi Zubair,
I'm leaving the pbix in a drive, let me know if you can't access it
Thanks and best regards
Gabriel
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
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
Thank you very much Zubair!!
I've learned something new!!
Amazing solutions 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |