Forum Discussion

dpbi's avatar
dpbi
Icon for Helper I rankHelper I
8 years ago

DISTINCT 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. ...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

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

dpbi's avatar
dpbi
Icon for Helper I rankHelper I
8 years ago

Hi 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