Distinct Count with filter

[Anonymous]

Hello Guys,

I am doing a distinctcount with filter on a column, power bi doesn't return any syntax error but there is no value in the result. I know for sure that there scould be something in there. here is the formula I am applying:

ACHIEVED GOAL = CALCULATE(DISTINCTCOUNT(DSM[Driver]), DSM[TOTAL MILES] = 3000).

can someone help me understand why there is no value in ACHIEVED GOAL?

Thanks,

Ulrich

[Anonymous]

Thank you all for your reply. please let me reformulate my inquiry. look at this table.

I have a total mile goal set by week which is 3000 for each driver. the total mile for each driver is the sum of miles for everyday. so my result was blank because power bi was evaluating mile/day there was no day where a driver reached 3000 miles. what I need your help with now is how to sum the daily miles in my filter? I am using this formular but it doesn't work.

thanks,

Ulrich

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have test with your measure, it works on my side:

It's suggested to first check your dataset by applying simple filter like "TOTAL MILES is 3000" as @HashamNiaz said.

If it does not make sense,please share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Eyelyn, I am doing this but mine is counting all the raws, including the blank ones. am I missing something?

it should be 16 instead of 24.

[Anonymous]

Hi @Anonymous ,

According to the screenshot,It seemed that you got the error count in Excel not in Power BI.

Please try :

• In Excel:

• In Power BI:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Driver1 ID]),FILTER('Table','Table'[Total MILES]>3000))

Refer to:

https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel

https://exceljet.net/formula/count-cells-that-are-not-blank

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HashamNiaz]

Hi @Anonymous !

Please try filtering your DSM table where [Total Miles] = 3000 from Data view and see if it retunrs any rows;

Alternatively you can try below DAX to check your measure;

ACHIEVED GOAL = CALCULATE(DISTINCTCOUNT(DSM[Driver]), FILTER(DSM, DSM[TOTAL MILES] = 3000))

 Regards,

Hasham

[parry2k]

@Anonymous the syntax looks ok, it will give you distinct count where Total Miles = 3000

if that condition is met you should get the count. Share the sample pbix or data to further look into it?

Do you have any other files in the report?

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡