Forum Discussion

RJSuttlemyre's avatar
RJSuttlemyre
Frequent Visitor
3 years ago
Solved

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 fli...
DAX Commands and Tips
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

RJSuttlemyre's avatar
RJSuttlemyre
Frequent Visitor
3 years ago

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's avatar
ERD
Icon for Community Champion rankCommunity Champion
3 years ago

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 )

 

  • RJSuttlemyre's avatar
    RJSuttlemyre
    Frequent Visitor
    3 years ago

    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

  • RJSuttlemyre's avatar
    RJSuttlemyre
    Frequent Visitor
    3 years ago

    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?