Forum Discussion

bhartsell's avatar
bhartsell
Frequent Visitor
9 years ago
Solved

Count per day in calculated table

I have two tables, one of which has logs that occur multiple times throughout the day, and the other is a Date table. They are connected through a relationship between the date columns. 

 

I'm trying to get a calculated column on the Date table that shows how many logs there are for any given day on the other table. How would I do this?

 

Thank you in advance! 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi bhartsell,

     

    Now that you have connected the two tables through a one to many relationship based on “date”. Please try this formula to create a calculated column:


    CountRows = COUNTROWS(RELATEDTABLE(Table_Log))

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi bhartsell,

     

    Now that you have connected the two tables through a one to many relationship based on “date”. Please try this formula to create a calculated column:


    CountRows = COUNTROWS(RELATEDTABLE(Table_Log))

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Hi There,

     

    Assuming that you have one to many relationship between your date table and log table,

     

    Use following DAX Formula for  count in Calculated column

     

    Count:=CALCULATE(COUNTROWS(log table))

     

     

    Thanks & Regards,

    Bhavesh

    • bhartsell's avatar
      bhartsell
      Frequent Visitor
      9 years ago

      Thanks for the reply!

       

      I've tried this, but when I do the result is that it counts the rows for all time, not just for whatever day the row is. 

      • BhaveshPatel's avatar
        BhaveshPatel
        Super User
        9 years ago

        Please post your data model and sample data for further check.

         

        Thanks & Regards,

        Bhavesh

  • lmichel's avatar
    lmichel
    Regular Visitor
    9 years ago

    Hello Power BI community,

     

    I have been working on this for days and tried different ways to get this done with no success. 

    1. The goal is visualize in a Clustered column chart how many tickets the company opened and closed per day. And I want to visualize this on the chart just for the 14 days.

    2. For this I have a Table with Tickets_Service (Int), Date_Entered (Date type) and Date_Closed(Date Type) columns. And I need to create two measures.

        - Per Tickets_Opened measure I need to count (Tickets_Service) based on Date_Entered per the last 14 days.

        - Per Tickets_Closed I measure I need to count (Tickets_Service) based on Date_Closed per last 14 days.

        - After that I need to visualize Tickets_Opened and Tickets_Closed measures per the last 14 days on the chart.

     

    Any ideas how I can get this visualization done?

     

    Thnak you in advance