Distinct count by criteria and time period

[Anonymous]

Hi all,

I have a table [Booking_data] that shows ID, day of the booking, booking type and the booking number.

I would like to know the distinct count of IDs that have made only A type bookings for a specific period of time. Then another metric which shows the distinct count of IDs who have made A and B bookings, but not any other type. 

For example: For October there is only one ID making an A type Booking (Customer number SL541879T1)

Can you help me with the DAX for those metrics?

Thank you in advance for the help!

ID	Day	Type	Booking#
FL375756M5	05/02/2019	A	BH2365
FL375756M5	05/03/2019	B	BH2378
FL375756M5	05/04/2019	C	BH2879
FL375756M5	06/12/2019	D	BH3014
HJ485762B4	02/18/2019	B	BH1894
HJ485762B4	04/25/2019	B	BK1478
HJ485762B4	08/20/2019	B	BH4897
HJ485762B4	10/04/2019	A	BK2874
VM897465S2	01/18/2019	A	BH1458
VM897465S3	03/04/2019	D	BH1987
VM897465S4	06/20/2019	C	BH1991
VM897465S5	11/21/2019	D	BH2348
VM897465S6	11/21/2019	A	BH2478
FG325879V0	08/09/2019	B	BH3974
FG325879V0	09/10/2019	B	BH4876
FG325879V0	09/28/2019	B	BH4187
SL541879T1	10/31/2019	A	BK2487
XS217894V3	02/08/2019	A	BH7788
XS217894V3	09/15/2019	A	BK4873

[MartynRamsden]

Hi @Anonymous 

Try adding these 3 measures - the 'Distinct ID (B Only)' measure is required in the computation of 'Distinct ID (A&B)':

Distinct ID (A Only) = 
CALCULATE ( 
    DISTINCTCOUNT ( 'Booking_data'[ID] ),
    'Booking_data'[Type] = "A"
)

Distinct ID (B Only) = 
CALCULATE ( 
    DISTINCTCOUNT ( 'Booking_data'[ID] ),
    'Booking_data'[Type] = "B"
)

Distinct ID (A&B) = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Booking_data[ID] ),
            "@A", [Distinct ID (A Only)],
            "@B", [Distinct ID (B Only)]
        ),
        [@A] > 0 && [@B] > 0
    )
)

Hope it helps!

Best regards,

Martyn

[Anonymous]

Hi @MartynRamsden and @Anonymous 

Thank you for helping, but sadly it didn't work.

That formula counts all IDs that have had at least one A type booking, but they may have other types as well. I need it to count distinct IDs that have only A and no other type. With that formula I get 6 distinct counts, when I should get 4 (IDs: VM897465S2, VM897465S6, SL541879T1 and XS217894V3):

ID	Day	Type	Booking#
FL375756M5	05/02/2019	A	BH2365
FL375756M5	05/03/2019	B	BH2378
FL375756M5	05/04/2019	C	BH2879
FL375756M5	06/12/2019	D	BH3014
HJ485762B4	02/18/2019	B	BH1894
HJ485762B4	04/25/2019	B	BK1478
HJ485762B4	08/20/2019	B	BH4897
HJ485762B4	10/04/2019	A	BK2874
VM897465S2	01/18/2019	A	BH1458
VM897465S3	03/04/2019	D	BH1987
VM897465S4	06/20/2019	C	BH1991
VM897465S5	11/21/2019	D	BH2348
VM897465S6	11/21/2019	A	BH2478
FG325879V0	08/09/2019	B	BH3974
FG325879V0	09/10/2019	B	BH4876
FG325879V0	09/28/2019	B	BH4187
SL541879T1	10/31/2019	A	BK2487
XS217894V3	02/08/2019	A	BH7788
XS217894V3	09/15/2019	A	BK4873

[MartynRamsden]

Sorry @Anonymous , I misunderstood - I didn't realise you were looking for explicitly A's only.

Try this instead:

Distinct ID (A Only) = 
VAR All_IDs = DISTINCTCOUNT ( Booking_data[ID] )
VAR NonA = 
CALCULATE ( 
    DISTINCTCOUNT ( Booking_data[ID] ),
    Booking_data[Type] <> "A"
)
VAR Result = All_IDs - NonA
RETURN Result

[Anonymous]

@MartynRamsden  Yes! It worked brilliantly!

Thank you so much for the help!

Can you please tell me what the formula would be for A and B type, but no other type?

[MartynRamsden · Accepted solution]

You're welcome @Anonymous 

Please try the measure below for A & B only, but no other type.

Distinct ID (A&B Only) = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Booking_data[ID] ),
            "@A", CALCULATE ( DISTINCTCOUNT ( 'Booking_data'[ID] ), 'Booking_data'[Type] = "A" ),
            "@B", CALCULATE ( DISTINCTCOUNT ( 'Booking_data'[ID] ), 'Booking_data'[Type] = "B" ),
            "@Other", CALCULATE ( DISTINCTCOUNT ( 'Booking_data'[ID] ), 'Booking_data'[Type] <> "A" && 'Booking_data'[Type] <> "B" )
        ),
        [@A] > 0 && [@B] > 0 && [@Other] = 0
    )
)

I haven't had time to test it so can't be sure it works.

Best regards,

Martyn

[Anonymous]

Works like a charm! Thank you so much 🙂 

[Anonymous]

Hi,

Try the following measures

for A only 

CALCULATE(DISTINCTCOUNT(Test[ID]),FILTER(Test,Test[Type] = "A"))

For A or B

CALCULATE(DISTINCTCOUNT(Test[ID]),FILTER(Test,Test[Type] = "A" || Test[Type] = "B"))