Distinct Count of Two Columns

[Anonymous]

Hello all, 

I have a Booking Table where I have a Booking ID and a Departure ID for each booking. 

I need a measure to calculate the no. of legs which should be the Distinct Depature ID for each Booking ID. 

I'm using the following DAX but I don't think I'm getting the correct figure

COUNTROWS(SUMMARIZE(Booking, Booking[Booking ID], Booking[Departure ID]))

Thanks in advance!

[amitchandak · Accepted solution]

@Anonymous , Try with Departure id?

COUNTROWS(SUMMARIZE(Booking, Booking[Booking Reference], Booking[Departure ID]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous · Accepted solution]

Hi @Anonymous ,

My sample data is this

The measure

DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Booking Table'[Booking ID] ),
    ALLEXCEPT ( 'Booking Table', 'Booking Table'[Departure ID] )
)

You can check details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Try with Departure id?

COUNTROWS(SUMMARIZE(Booking, Booking[Booking Reference], Booking[Departure ID]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.