Checking measures

DAX DAX Calculations
1

Dear Sir

Can you kindly check me for below two measures which produce the same results currently for as much as I tested on different context?

The first one is taking reference from RADACAD Youtube channel ( https://www.youtube.com/watch?v=kSy-7UHe4HE ) and the second one is I created myself which seems much more simpler. But I am afraid that my second one can be wrong in some context I can’t imagine now. Would you kindly check for me, please? The main difference is the former one uses Filter and Addcolumns and the later one just directly use Filter.

#Active Users =
VAR CurrentDate = Max(‘Calendar’[Date])

VAR CustomersTable =
ADDCOLUMNS(
Customer,
“Sales In Specific Period”,
CALCULATE(
[Total Sales],
FILTER(
All(‘Calendar’[Date]),
‘Calendar’[Date] <= CurrentDate &&
‘Calendar’[Date] > CurrentDate - [Number of Days Value] )),
“Cumulative Sales”,
[Cumulative Sales Over Date])

VAR ActiveUsers =
COUNTROWS(
FILTER(
CustomersTable,
[Sales In Specific Period] > 0 ))

Return
ActiveUsers

#Active Users 2 =
VAR CurrentDate = Max(‘Calendar’[Date])

VAR SalesInSpecificPeriod = Filter( Customer,
CALCULATE(
[Total Sales],
FILTER(
All(‘Calendar’[Date]),
‘Calendar’[Date] <= CurrentDate &&
‘Calendar’[Date] > CurrentDate - [Number of Days Value] )) > 0)

VAR ActiveUsers =
COUNTROWS(
SalesInSpecificPeriod )
Return
ActiveUsers

2

Do you need cumulative sales?
Also I would suggest trying them out for performance, it may be that add columns is quicker to generate the table, where as filter may be slower as it scans a table and when doing so has to generate the measure for each row and then filter, generating the whole column and then filtering a pre calculated column may be faster, but you would need to check it out.

If you dont need cumulative totals, I would put both measures into a visual, make a new measure measure1 - measure 2, if the result is 0 then choose the better performing measure :slight_smile:

1 Like
3

Oh yes. I should have put the cumulative sales for the active user 2 measures too. Sorry for getting them confused.

We need the cumulative sales, especially when we are trying to get returning users in which Sales in Specific Period is less than cumulative sales and new users in which Sales in Specific Period is equal to cumulative sales.

May I write for returning users for comparison;

The measure I learnt from youtube channel is below ;

#Returning Users ( Youtube Ways ) =
VAR CurrentDate = Max(‘Calendar’[Date])

VAR CustomersTable =
ADDCOLUMNS(
Customer,
“Sales In Specific Period”,
CALCULATE(
[Total Sales],
FILTER(
All(‘Calendar’[Date]),
‘Calendar’[Date] <= CurrentDate &&
‘Calendar’[Date] > CurrentDate - 90 )),
“Cumulative Sales”,
[Cumulative Sales Over Date])

VAR ReturningUsers =
COUNTROWS(
FILTER(
CustomersTable,
[Sales In Specific Period] > 0 && [Sales In Specific Period] < [Cumulative Sales] ))

Return

If(
[Sales in specific period] > 0,
ActiveUsers )

And then, I tested my way as below. I firstly created the measures for both Sales in Specific Period and Cumulative Sales.

Sales in specific period =
CALCULATE(
[Total Sales],
FILTER(
All(‘Calendar’[Date]),
‘Calendar’[Date] <= Max(‘Calendar’[Date]) &&
‘Calendar’[Date] > Max(‘Calendar’[Date]) - 90 ))

Cumulative Sales Over Date =
CALCULATE(
[Total Sales],
FILTER(
All(‘Calendar’),
‘Calendar’[Date] <= Max(‘Calendar’[Date])))

Returing Users ( My Ways ) =
COUNTROWS(
FILTER(
Customer,
[Sales in specific period] > 0 && [Sales in specific period] < [Cumulative Sales Over Date]))

I feel that my way is quite simpler but feel that it can lead to some error out of some context.

Looking forward to having your kind suggestion on that.

4

Looking at the measurers they seem to do the same thing, one thing I would say is the top measure may calculate the virtual table once and use it for all rows in the visual, where as the bottom measure may have to generate the table for each item in the visual requiring more queries and measure that performs worse.

Did you try to create a new measure than deducts the first measure from the second, and see if it returns 0 for all rows? if it does you should be able to to choose which ever measure you feel performs best.

The only change I would possible make is to use an AND() statement as there are only 2 criteria, it may run better, but test both measures produce the same result.

2 Likes
5

Hello @KhinKyeSin

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

6

May I inform it is marked as a solution?

7

Thank you for your solution.

1 Like