topic Re: How to count rows based on two names in DAX Commands and Tips

How to count rows based on two names

RJSuttlemyre — Fri, 01 Sep 2023 13:53:09 GMT

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.

Count of IdFlight with Both RNAV Approach and UA =

COUNTROWS(

FILTER(

SUMMARIZE('Event Groups', 'Event Groups'[IdFlight]),

COUNTROWS(

FILTER(

'Event Groups',

'Event Groups'[IdFlight] = EARLIER('Event Groups'[IdFlight]) &&

('Event Groups'[Name] = "RNAV Approach" || 'Event Groups'[Name] = "UNSTABLE APPROACH - DB")

)

Re: How to count rows based on two names

ERD — Fri, 01 Sep 2023 15:29:41 GMT

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" )

Re: How to count rows based on two names

RJSuttlemyre — Fri, 01 Sep 2023 16:29:14 GMT

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?

Re: How to count rows based on two names

ERD — Fri, 01 Sep 2023 16:34:57 GMT

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

Re: How to count rows based on two names

RJSuttlemyre — Fri, 01 Sep 2023 19:16:22 GMT

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"

Re: How to count rows based on two names

ERD — Mon, 04 Sep 2023 06:24:53 GMT

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 )

Re: How to count rows based on two names

Anonymous — Mon, 04 Sep 2023 07:41:51 GMT

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.

Re: How to count rows based on two names

RJSuttlemyre — Thu, 14 Sep 2023 16:34:24 GMT

Thanks so much. Sorry for the late reply, but I had a death in the family and had to go on emergency leave. I have some work stacked up, but will try this in the next couple of days. Much appreciated

Re: How to count rows based on two names

RJSuttlemyre — Thu, 14 Sep 2023 17:27:49 GMT

I just tried this and it looks like it is counting the UA-DB instead of the IdFlights that have each.

I make some cards to just check each of the measures. I would expect the intersection of them to be in the 5-10K range. Any ideas?