cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmmarkk01
Frequent Visitor

Counting occurences of dates between table and datetable

Dear PowerBI community,

 

I am struggling with the following:

 

Table 1: Date Table

Date table with all unique dates

iddate_full
107-01-1970
208-01-1970
etcetc

 

Table 2: Table with data

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

 

idnamestart
1Task A07-01-1970
2Task B07-01-1970
3Task C07-01-1970
4Task D08-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)

iddate_fullstart_occurences
107-01-19703
208-01-19701

 

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

 

Kind regards,

 

Mark

1 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:

SteveHailey_0-1643310154757.png

Table 3:

SteveHailey_1-1643310248024.png

New column in Table 1:

SteveHailey_2-1643310274135.png


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.

View solution in original post

6 REPLIES 6
SteveHailey
Solution Specialist
Solution Specialist

Hello @mmmarkk01


You could use:

 

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

 

SteveHailey_0-1643207495601.png  

 

SteveHailey_2-1643207721916.png

 

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

 

-Steve

 

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..

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' )
)

 

SteveHailey_0-1643210641080.png

 

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

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:

SteveHailey_0-1643310154757.png

Table 3:

SteveHailey_1-1643310248024.png

New column in Table 1:

SteveHailey_2-1643310274135.png


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.

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!