Forum Discussion

Chamari's avatar
Chamari
New Member
11 months ago
Solved

Creating column in date table that counts rows per month if they meet a criteria

Hi all,   I have a date table and would like a count per month of rows from another table if they contain "Y".   The date table looks something like this: Date Table Date YearMonthNumber ...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    11 months ago

    Hi Chamari,

     

    Issue is with the DAX you are using, use below DAX to create new calculated column in date table 

     

    RaisedbyResCount =
    CALCULATE(
    COUNTROWS(RELBs),
    RELBs[RaisedbyRes] = "Y",
    RELBs[YearMonthNumber] = DateTable[YearMonthNumber]
    )

     

    Or you can use below measure if there's relationship between your date table and RELBs

     

    RaisedbyResCount =
    CALCULATE(
    COUNTROWS(RELBs),
    RELBs[RaisedbyRes] = "Y"
    )

     

    🌟 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!

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
11 months ago

Hi Chamari,

 

Issue is with the DAX you are using, use below DAX to create new calculated column in date table 

 

RaisedbyResCount =
CALCULATE(
COUNTROWS(RELBs),
RELBs[RaisedbyRes] = "Y",
RELBs[YearMonthNumber] = DateTable[YearMonthNumber]
)

 

Or you can use below measure if there's relationship between your date table and RELBs

 

RaisedbyResCount =
CALCULATE(
COUNTROWS(RELBs),
RELBs[RaisedbyRes] = "Y"
)

 

🌟 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!

Chamari's avatar
Chamari
New Member
11 months ago

Thank you so much,

 

I had to remove the last part from the DAX ( = DateTable[YearMonthNumber]) to get it to work but this has worked, thank you 🙂