Help needed on Count formula by date instead of each Job#

[Ross1991]

Date     Shift   Job#

Jan3       1         X

Jan3        1        Y

Jan4        2        Y

Jan4        1        X

Trying to get a shift count for above example. Same date should count as 1 time.   Correct answer will be Shift 1 = 2 times,   Shift 2 = 1 time

I don't want shift 1 being counted twice on Jan 3.  Thanks in advance for the formula to consider.

[goncalo_ricardo_pereira]

Hi @Ross1991 ,

I would solve the situation via script... Like create a new field in the data model that we can count or sum in the frontend without many complications. As an example, if you create a new column that is the concatenation of the date field and shift field, you could then use the following expression:

Your table:

Date       Shift      Job#       KeyCount
Jan3         1           X            Jan3|1
Jan3         1           Y            Jan3|1
Jan4         2           Y            Jan4|2
Jan4         1           X            Jan4|1

Your expression:

Count(distinct KeyCount)

Hope it helps.

Best Regards

Goncalo Pereira

[Vegar]

You should be able to do this without any script changes. 

Depending on what you need, try this measure

=Count ( Distinct Date)

or try this measure 

=Count (Distinct Shift & Date)

[Ross1991]

Following formula worked for me.  I did it for each shift.  Below is for shift 1.

{<[shift]={'1st'}>}count(distinct[date])