Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Need help getting an active count measure

Hi, I posted something similar recently and need to know how to make some slight changes for a different result. I need to know the number of Counties that are active in each quarter. Using the table...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi RichOB,

     

    You can add a YearQuarter column to your existing Calendar table like below

     

    YearQuarter =
    'Calendar'[Year] & " Q" & 'Calendar'[Quarter]

     

    And then update your measure as 

     

    Active Counties =
    CALCULATE (
    DISTINCTCOUNT ( Properties[County] ),
    FILTER (
    Properties,
    Properties[Created_date] <= MAX ( 'Calendar'[Date] ) &&
    (
    ISBLANK ( Properties[Decommissioned_Date] ) ||
    Properties[Decommissioned_Date] > MIN ( 'Calendar'[Date] )
    )
    )
    )

     

    This way you don't have to add table/column manually and it'll be updated dynamically.

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
1 year ago

RichOB - If you'd like an answer to this, more information is necessary. 

 

Let's start with:

 

1) was your last question answered? 

2) if yes, can you post the link?

3) do you have a date table that defines the Quarters?

4) does this date table have a relationship to the table with your counties and decomissioned dates?

5) what DAX have you tried so far?