Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
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 |
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
@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?
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
Works like a charm! Thank you so much 🙂
Hi,
Try the following measures
for A only
For A or B
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |