Forum Discussion

david_blabla's avatar
david_blabla
Frequent Visitor
6 years ago
Solved

Count rows by date on unconnected sources

Hi there!

 

I have two unconnected sources in my BI file:

ActivitiesFinished on
11.1.2020
21.1.2020
32.1.2020
42.1.2020
53.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

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

6 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      david_blabla
      Frequent Visitor
      6 years ago
      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's avatar
        JarroVGIT
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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 🙂