topic Re: Distinct count with date parameter in DAX Commands and Tips

Distinct count with date parameter

njxfoster — Mon, 28 Feb 2022 22:06:12 GMT

Hi Guys

I want to create a distinct count for cancel note

These "Request from Management - pick up resource hours" should only be count once since it's all from the same date but different shift.

However, If I only use distinct count in this situation. "Request from Management - pick up resource hours" from other date will all count as only one note in total

Is there anyway I can count this cancel note also with parameter of different date so "Request from Management - pick up resource hours" from other date will count seperatly?

Thanks In advance.

Re: Distinct count with date parameter

Whitewater100 — Tue, 01 Mar 2022 11:37:48 GMT

Hello:

What is your expected result?

Can you try for the first measure:

Cancel Note CT = DISTINCTCOUNTNOBLANK(Count_Table[Cancel_Note])

Then to Count the days :

Count Measure = SUMX(

VALUES(Count_Table[shift]),

[Cancel Note CT])

Re: Distinct count with date parameter

njxfoster — Wed, 02 Mar 2022 21:19:27 GMT

Hi! Thank you for your answer

For example:

If I use distinctcountnoblank. the result for "request from managemnt" will only show 1

I want to add condition to the measure so it count this "request from management" as 3 since there are three different agents who share same cancel reason.

Re: Distinct count with date parameter

Whitewater100 — Wed, 02 Mar 2022 22:00:12 GMT

OK. You can put calc column in table:

Count =

var _name = Data[Name]

var _note = Data[Cancel_Note]

return

COUNTX(

FILTER(

ALL(Data),

Data[Cancel_Note] = _note &&

Data[Name] = _name)

, Data[Cancel_Note])

Then measure:

Manager CT = CALCULATE(DISTINCTCOUNT(Data[Name]),

Data[Count] >0)

Does this work for what you need?

Re: Distinct count with date parameter

v-janeyg-msft — Thu, 03 Mar 2022 03:24:09 GMT

Hi, njxfoster

The sample data you share is not representative, Does the cancel_note column have only one value? The same agent_name appears on only one day? Please describe your needs completely without your subjective operational awareness. In this way we can give an accurate formula.

Do you want to count how many different dates have cancel_note under the current agent_name?

Best Regards,
Community Support Team _ Janey