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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Distinct Count of Two Columns

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!
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

View solution in original post

v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,

 

My sample data is this

Screenshot 2021-04-06 145911.png

 

The measure

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

Screenshot 2021-04-06 150322.png

 

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.

 

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,

 

My sample data is this

Screenshot 2021-04-06 145911.png

 

The measure

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

Screenshot 2021-04-06 150322.png

 

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
Super User
Super User

@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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.