Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count DISTINCTVALUE with a FILTER for a specific date

Hi,

 

I have a created measure that I believe should be correct but it is not.

 

I would like to count the DISTINCTCOUNT of the field 'Eligibility Data'[Member ID (Elig)] with a filter for the specific date of 11/30/2020 in the field 'Eligibility Data'[Coverage Date End (Elig)].

 

Here is my existing created measure that I've come up with (but it is not returning a value):

 

Distinct Count of Eligibile Members =

CALCULATE(DISTINCTCOUNT('Eligibility Data'[Member ID (Elig)]), FILTER('Eligibility Data','Eligibility Data'[Coverage Date End (Elig)] = "11/30/2020"))
 
When I originally bring the data in to Power BI the 'Eligibility Data'[Coverage Date End (Elig)] field is brought in as a text field.  I've gone ahead and transformed the field in Edit Queries from a text field to a Date field.
 
Is this why my formula is not acknowleging "11/30/2020" as a date?  Is it looking for a text value with "11/30/2020" when I've already transformed it to a date field?
 
If so, how do I modify my existing created measure to look up 11/30/2020 as a date and not a text value?
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi Anonymous 

    Sure, it will be looking for that string. Use

    DATE(2020, 11, 30)

    instead of

    "11/30/2020"

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Sure, it will be looking for that string. Use

    DATE(2020, 11, 30)

    instead of

    "11/30/2020"

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers