Count rows by date on unconnected sources

[david_blabla]

Hi there!

I have two unconnected sources in my BI file:

Activities	Finished on
1	1.1.2020
2	1.1.2020
3	2.1.2020
4	2.1.2020
5	3.1.2020

And a calendar including all dates that the project was executed over.

Now I try to create a bar chart that shows per day how many activities were finished on that day. I now created a new column in the calendar table:

Finished Activities = COUNTROWS(FILTER(ALLSELECTED(activities); activities[actual_finished].[Date] = 'Calendar'[Date].[Date]))

It works and I can see the chart as it should be. But unfortunately I can not filter it e.g. for Location which is something that is also stated in the activities table.

I think I need to create a measure in the activities table which basically does the same as my table on the calendar currently but I do not know how.

Any ideas?

Thanks,

David

[JarroVGIT]

Hi @david_blabla ,

You explicitely state that these tables (your calendar table and your activity table) are unrelated, but you could relate them on the Date column. If you create that relationship, you can pull in the date from the calendar table as axis in your visual and the Task column as values (make sure you set it as Count). Then, you can actually filter and slice based on other columns in your dataset. 

Note that calculated columns are evaluated once and not again when applying a slicer. 

Creating agregating columns in a calendar table is bad practice and should be avoided at all times! If you need help on any of the steps above and Google is not helping you, please let me know 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[david_blabla]

Thanks for your reply!
What if I have multiple date columns on the activities table - planned, finished, confirmed, ... - Do I then map all of them to the calendar or is one enough?

Will try to get it working tomorrow again 🙂

[JarroVGIT]

You can only have one active relationship so if you don't need relationships in the rest of your report, just create one on the finished column 🙂

[david_blabla]

I tried connecting the two tables but it does not help me since I want to be able to comapre Planned vs. Finished vs. Confirmed per day.

I use the following measure to create a very similar chart which shows Planned vs. Finished vs. Confirmed cumulative in a line chart.

Cumulative Finished = CALCULATE(COUNT(activities[actual_finished]);FILTER(ALLSELECTED(activities);activities[actual_finished] <= MAX('Calendar'[Date])))

I think I basically need to create a similar measure that cumulates not from the beginning of the project until the date shown in the chart but per date shown in the chart separately.

Does that make sense?

[v-kelly-msft · Accepted solution]

Hi @david_blabla ，

You only need to modify your dax expression as below:

Cumulative Finished = CALCULATE(COUNT(activities[actual_finished]);FILTER(ALLSELECTED(activities);activities[actual_finished] =selectedvalue('Calendar'[Date])))

Then you can compare the values per day.

Best Regards,
Kelly

[david_blabla]

Awesome, now I have a table that shows the same values for my "column in calendar workaround" as well as your dax statement.

For some reason tho the bar chart does show values for my "column in calendar workaround" but not anything if I include the dax statement.

Any idea why?