Forum Discussion

LR_PublicHealth's avatar
LR_PublicHealth
Regular Visitor
2 years ago
Solved

Only counting an ID once for each date it appears.

  Hello, everyone!  Having used a different BI tool for many years, our team is making the move to Power BI. I'm steadily getting used with it, but I've hit a bit of a stumbling block. I'm sure i...
Need Help
  • audreygerred's avatar
    audreygerred
    2 years ago

    Hello! I entered the sample data as below:

    I created the following DAX: 

    Count = COUNTROWS(
        SUMMARIZE(
            FILTER('Table',
                'Table'[Outcome] = "New" || 'Table'[Outcome] = "Repeat"
            ),
            [ID], [Date]
        )
    )
     
    And, I get the following count of 6 🙂
     

     

     

audreygerred's avatar
audreygerred
Icon for Super User rankSuper User
2 years ago

You are very welcome! Power BI is a great tool. It takes, some time but there are so many great references. Good luck on your journey!

LR_PublicHealth's avatar
LR_PublicHealth
Regular Visitor
2 years ago

Very sorry to bother you, Audrey.

All was going well until I got to a specific indicator:

Indicator 13.1 = 
COUNTROWS(
    SUMMARIZE(
        FILTER('table',
           'table'[Outcome] <> "NEW" || 'table'[Outcome] <> "REVIEW" || 'table'[Age] < 19
        ),
        'table'[ID], 'table'[Date]
    )
)


The [Age] column is another measure, like so:

Age = ROUNDDOWN(DATEDIFF('table'[BirthDate], TODAY(), DAY) / 365.25, 0)

For some reason, the indicator is counting everybody, rather than just patients who do not have those outcomes and are under 19 years of age. If I remove the age part of the filter, there is no change in the end count. Am I missing something really simple?

Thanks in advance.

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    2 years ago

    Hi! Can you add a column for age in Power Query? That should fix the issue.