Counting occurences of dates between table and datetable

[mmmarkk01]

Dear PowerBI community,

I am struggling with the following:

Table 1: Date Table

Date table with all unique dates

id	date_full
1	07-01-1970
2	08-01-1970
etc	etc

Table 2: Table with data

Table which contains a date field called start and the values are not unique.

id	name	start
1	Task A	07-01-1970
2	Task B	07-01-1970
3	Task C	07-01-1970
4	Task D	08-01-1970

Looking for the following result:

Creation of a new table that looks like:

The reason for a new table is I want to add more information linked to these given dates (like finish, actuals etc)

id	date_full	start_occurences
1	07-01-1970	3
2	08-01-1970	1

Any help or pointer in the right direction is much appreciated!

Kind regards,

Mark

[SteveHailey]

Hello @mmmarkk01. 

You could use:

start_occurences = COUNTROWS( 'Table 2: Table with data' )

  

I attached a .pbix file here, in case it's helpful.

-Steve

[mmmarkk01]

Hi Steve, this works for the linked fields, but I am looking to extend this beyond just the column start. So not sure a measure is the way to go for me. I tried this with a new column but that doesnt work..

[SteveHailey]

Are you wanting to add the column to the date table? If so, wrap the COUNTROWS in a CALCULATE statement so that there is context transition. 

start_occurences_column = 
CALCULATE(
    COUNTROWS('Table 2: Table with data' )
)

[mmmarkk01]

Hi Steve, hwo would I do this for different columns? It seems it only catches the linked columns?

[SteveHailey · Accepted solution]

Hi @mmmarkk01. I'm not positive I understand what you mean.
I think you may mean that the new column in Table 1 is working to count the rows in Table 2 that have the same date. And it's working because there is a relationship between the date colummns in Table 1 and Table 2.
However, you have a 3rd table that does not have a relationship to Table 1, and for some reason you don't want to create a relationship between Table 1 and Table 3. But you do want a similar column to count the rows in Table 3 that have the same date as Table 1. 
If this is true, you could use TREATAS, which would look something like this:

Count from Table 3 =
CALCULATE (
    CALCULATE (
        COUNTROWS ( 'Table 3: Unlinked table' ),
        TREATAS (
            VALUES ( 'Table 1: Date Table'[date_full] ),
            'Table 3: Unlinked table'[date]
        )
    )
)

However, best practice would be to just create a relationship between the tables.

Here are some screenshots:

Data model:

Table 3:

New column in Table 1:

Here's a link to an updated sample .pbix file.

If I'm misunderstanding, please try to be more specific about what you're trying to do, and I'll be happy to help you get this figured out.

[mmmarkk01]

Hi Steve, I am going to try this, will let you know the outcome.