Count per day in calculated table

[bhartsell]

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! 

[lmichel]

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

[v-yulgu-msft · Accepted solution]

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]

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]

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]

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

Thanks & Regards,

Bhavesh