Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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' )
)
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:
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.
Hi Steve, I am going to try this, will let you know the outcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |