Forum Discussion
How to count rows based on two names
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.
Community ChampionHi 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?
- ERD
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
You bet. I can't turn over a file because it's proprietary, but here is the model
Here's the example of the Event Groups. As you can see, some IdFlights have multiple events and some have one:
So, I just want to count only the IdFlights that has the combination two specific "names" in the 'Names' column: "RNAV Approach" and "Unstable Approach - DB"
- ERD
RJSuttlemyre , so you need ids with both values present. Try this
amt = VAR t = FILTER ( ADDCOLUMNS ( VALUES ( 'Event Groups'[IdFlights] ), "names", CALCULATE ( DISTINCTCOUNT ( 'Event Groups'[Names] ), 'Event Groups'[Names] IN { "UNSTABLE APPROACH - DB", "RNAV Approach" } ) ), [names] = 2 ) RETURN COUNTROWS ( t )