New DAX measure filtering by distinct count and two numeric variables

[krlowe]

Hi folks,

I'm trying to create a percentile calculation in the end, but here are some details.

Table name=DTaP

Need to create new metric called "Vaccine at 2 Months" with the following filtering:

-distinctcount of ID variable

-Filter AgeMonths variable is less than or equal to 2

-Filter OrdinalPosition variable equals 1

Overall, I need to only take into consideration distinct IDs and filter across two numeric variables. I can't quite determine how to calculate and filter across all of these variables in DAX. Your help is much appreciated!

Thanks for the help!!!

[Anonymous · Accepted solution]

Thanks for rsbin and Kedar_Pande's concern about this issue.

Hi, @krlowe 

I am glad to help you.

Based on your description and the suggestions of others, I have created a simple pbix file that I hope will help you.

First, since you didn't give any test data, I created some of my own for testing:

Then create a Measure Vaccine at 2 Months:

Vaccine at 2 Months = 
CALCULATE(
    DISTINCTCOUNT(DTaP[ID]),
    DTaP[AgeMonths] <= 2,
    DTaP[OrdinalPosition] = 1
)

Output:

I have attached this pbix file below, hope it helps you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@krlowe 

Measure:

Vaccine at 2 Months = 
CALCULATE(
DISTINCTCOUNT(DTaP[ID]),
DTaP[AgeMonths] <= 2,
DTaP[OrdinalPosition] = 1
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[elitesmitpatel]

If @rsbin answer is not correct then please provide the dummy pbix file for easy reference and faster solution.

[rsbin]

@krlowe ,
Please try somthing like this (I am assuming you are referring to "variables" as your column fields 😞

Vaccine_at2Months = CALCULATE( DISTINCTCOUNT( DTaP[ID] ),
                    FILTER( DTaP, DTaP[AgeMonths] <= 2 &&
                                  DTaP[OrdnalPosition] = 1 ))

If this does not provide the solution you are looking for, please paste a sample of your data (as a table, not as a picture).

Hope this gets you started.

Regards and Happy Holidays,

[krlowe]

Thanks all - I appreciate the help!!