Forum Discussion
How to count rows based on two names
Hi all -- a little help if you could...
I have a table called "Event Groups" with two columns.
One column has a column called "IdFlight", which is a nominal variable (number of a airline flight) on which different events can occur. The IdFlight column can have one or more rows depending on how many reportable events occur on each flight.
The second column is called "Name", which has the names of the different reportable events.
I am trying to create a measure which counts the number of occurrences of IdFlight which have two specific event names (has to have both to be counted).
Here is what I have so far. Can someone help and point out where I'm going wrong? I think it has to have both Summarize and Earlier commands, but I'm not sure.
Hi RJSuttlemyre ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
Measure = var a=SUMMARIZE('Event Groups','Event Groups'[IdFlight],"Count",COUNTROWS(FILTER('Event Groups',[IdFlight] in VALUES('Event Groups'[IdFlight])&&[Name] in {"RNAV Approach","UNSTABLE APPROACH - DB"}))) return SUMX(FILTER(a,[IdFlight] in VALUES('Event Groups'[IdFlight])),[Count])(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi RJSuttlemyre , where are you going to use your measure?
You can try this:
Measure = CALCULATE ( DISTINCTCOUNT ( 'Event Groups'[IdFlight] ), 'Event Groups'[Name] = "RNAV Approach" || 'Event Groups'[Name] = "UNSTABLE APPROACH - DB" )Thanks so much. I'm going to use the measure to create a line graph to show the number of the measure per month.
The event is when a crew is doing a RNAV approach (read as GPS approach), but then also is unstable (doesn't meet altitude and airspeed parameters).
That said, I don't think it's working right. With the "or" command, I get all of the flights that have one or the other, so I end up with more flights than if I just count the RNAV approachs. I tried "&&" instead and I end up with Blank.
So, I really want to count the IdFlight that is doing an RNAV approach that is then is also classified as a UA (unstable). The problem is that both RNAV and UA is in the same 'Name' column.
Make sense?
Does that make sense?
It will make sense if you provide a sample data and a desired result. Please, use this article for reference: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/2425574#M64409
Hi RJSuttlemyre ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
Measure = var a=SUMMARIZE('Event Groups','Event Groups'[IdFlight],"Count",COUNTROWS(FILTER('Event Groups',[IdFlight] in VALUES('Event Groups'[IdFlight])&&[Name] in {"RNAV Approach","UNSTABLE APPROACH - DB"}))) return SUMX(FILTER(a,[IdFlight] in VALUES('Event Groups'[IdFlight])),[Count])(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
