cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper V
Helper V

DAX to calculate the count of the flags

Hi All,

I have a table named student with columns DateBooked,ParticipantName and NoBookingCheckFlag. This data is corresponding to the students reservation for a school program.

 

The Date Booked column shows the booking date of the ticket,ParticipantName is the name of the student and NoBookingCheckFlag column shows those students who attended the program without reservation so if the flag is set to Yes then they attended the program without reservation and if flag is set to No the students attended the program with reservation.

 

Expected results :

I need to create a calculated measure column to find the remaining columns OverAllCount(with/without reservation,count(with reservation) and  %ge ratio corresponding to the respective booking dates. 

 

% age ratio = 1- count(with reservation)/OverallCount(with/without reservation)

Can you please suggest suitable DAX to handle the expected results?

 

Input source :

Date

Booked

Participant

Name

 NoBookingCheckFlag OverallAllCount(with/withoutreservation)Count(WithReservation)%geRatio
15.09.2021Tom       Yes   
16.09.2021Tom       Yes   
15.09.2021John       Yes   
16.09.2021John       No   
20.09.2021Mary       Yes   
21.09.2021Mary       No   
17.09.2021Mary       No   
13.09.2021Elizabeth      Yes   
14.09.2021Elizabeth      Yes   
09.09.2021Mario      Yes   
10.09.2021Mario      Yes   
17.09.2021Mario      No   
20.09.2021Mario      No   
21.09.2021Mario     Yes   

 

Kind regards

Sameer

1 ACCEPTED SOLUTION
goncalogeraldes
Solution Supplier
Solution Supplier

Hello there @deb_power123 ! If I understood your problem correctly, this is what you want:

Count (with reservation) =
CALCULATE(
COUNTROWS(Table),
Table[NoBookingCheckFlag] = "No")

OverallCount =
COUNT(Table[NoBookingCheckFlag])

% age ratio = 1 - DIVIDE( Count (with reservation), OverallCount)

As a side note, calculated measure column does not exist in PBI terminology since calculated column <> measure. Check out this link on the differences between both calculations.

 

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

View solution in original post

3 REPLIES 3
goncalogeraldes
Solution Supplier
Solution Supplier

Hello there @deb_power123 ! If I understood your problem correctly, this is what you want:

Count (with reservation) =
CALCULATE(
COUNTROWS(Table),
Table[NoBookingCheckFlag] = "No")

OverallCount =
COUNT(Table[NoBookingCheckFlag])

% age ratio = 1 - DIVIDE( Count (with reservation), OverallCount)

As a side note, calculated measure column does not exist in PBI terminology since calculated column <> measure. Check out this link on the differences between both calculations.

 

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

View solution in original post

It worked

Tanushree_Kapse
Solution Supplier
Solution Supplier

Hi @deb_power123 ,

 

Please use below measures:

 

OverAllCount(with/without reservation)= COUNT(NoBookingCheckFlag)

Count(with reservation)= CALCULATE(COUNT(NoBookingCheckFlag), Table(NoBookingCheckFlag)= "No")

 %ge ratio= 1- DIVIDE(Count(with reservation), OverAllCount(with/without reservation))

 

 

Mark this as a solution if I answered your question.

Thanks.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!