Forum Discussion
Helper IDISTINCT COUNT and FILTER problem
Hi.
- I have ‘Sales’ table with 30K rows and 8 distinct items (as text).
- For each row ,I need to count distinct items in a range of 6 rows which are - the current row and the following 5 rows.
- The result of the calculation is shown in the ‘Distinct’ column.
- I have tried the following code (see below) in a calculated column, but the run time of the query is about 5 minutes for both of the examples. I aslo tried to seperate the filters but it didn't help.
My question
Is there alternative code to reduce query time?
Code 1
CALCULATE (
DISTINCTCOUNT ( Sales [ Item ] ) ,
Sales ,
AND (
Sales [ Index ] <= EARLIER ( Sales [ Index ] ) ,
Sales [ Index ] >= EARLIER ( Sales [ Index ] ) - 5
)
)
Code 2
VAR IndexColumn = Sales [ Index ]
RETURN
CALCULATE (
DISTINCTCOUNT ( Sales [ Item ] ) ,
Sales ,
AND (
Sales [ Index ] <= IndexColumn,
Sales [ Index ] >= IndexColumn - 5
)
)
2 Replies
v-yuta-msftCommunity Support
Hi dpbi,
The earlier function will be calculated in each row context in Code1, it will slow the performance. In addition, in your Code 2, the second parameter “Sales” is recognized a filter by DAX engine so it will also slow down the performance.
Try this DAX formula below:
distinct =
VAR IndexColumn = Sales[Index]
RETURN
CALCULATE (
DISTINCTCOUNT (Sales[Item]),
AND (
Sales[Index] <= IndexColumn,
Sales[Index] >= IndexColumn - 5
)
)
Best Regards,
Jimmy Tao
dpbiHi Jimmy.
Thanks for your response.
I tried your suggestion but it didn't work because i get "1" in all the rows.
The 'Sales' parameter is the name of the table , and without using it in the FILTER section of the CALCULATE function, i get "1" as result in all of the rows , in both examples i wrote.
Best Regards
Dan