Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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.
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.
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"
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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-Qu...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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"
@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 )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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?
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |