Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Distinct count by criteria and time period

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!

 

IDDayTypeBooking#
FL375756M505/02/2019ABH2365
FL375756M505/03/2019BBH2378
FL375756M505/04/2019CBH2879
FL375756M506/12/2019DBH3014
HJ485762B402/18/2019BBH1894
HJ485762B404/25/2019BBK1478
HJ485762B408/20/2019BBH4897
HJ485762B410/04/2019ABK2874
VM897465S201/18/2019ABH1458
VM897465S303/04/2019DBH1987
VM897465S406/20/2019CBH1991
VM897465S511/21/2019DBH2348
VM897465S611/21/2019ABH2478
FG325879V008/09/2019BBH3974
FG325879V009/10/2019BBH4876
FG325879V009/28/2019BBH4187
SL541879T110/31/2019ABK2487
XS217894V302/08/2019ABH7788
XS217894V309/15/2019ABK4873
1 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

View solution in original post

7 REPLIES 7
MartynRamsden
Solution Sage
Solution Sage

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
Not applicable

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):

 

IDDayTypeBooking#
FL375756M505/02/2019ABH2365
FL375756M505/03/2019BBH2378
FL375756M505/04/2019CBH2879
FL375756M506/12/2019DBH3014
HJ485762B402/18/2019BBH1894
HJ485762B404/25/2019BBK1478
HJ485762B408/20/2019BBH4897
HJ485762B410/04/2019ABK2874
VM897465S201/18/2019ABH1458
VM897465S303/04/2019DBH1987
VM897465S406/20/2019CBH1991
VM897465S511/21/2019DBH2348
VM897465S611/21/2019ABH2478
FG325879V008/09/2019BBH3974
FG325879V009/10/2019BBH4876
FG325879V009/28/2019BBH4187
SL541879T110/31/2019ABK2487
XS217894V302/08/2019ABH7788
XS217894V309/15/2019ABK4873

 

 

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
Not applicable

@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

Anonymous
Not applicable

Works like a charm! Thank you so much 🙂 

Anonymous
Not applicable

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"))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.