Forum Discussion
Measure to create relationship between two columns
I have a report that uses the Logged date dimenson table to filter specific dates to see hours logged. With my current relationships the tasks table will be filtered when I set a slicer of logged date to tasks which have had logged dates in that range. But when I bring in to count task ids I would like it to ignore the relationship logs table to tasks table and use a relationship from date table to task created date.
In power bi my tables look like this:
For example I have a Task table:
| Task Id | Created Date |
| 1 | 01/10/2019 |
| 2 | 02/10/2019 |
| 3 | 03/10/2019 |
| 4 | 04/10/2019 |
| 5 | 02/10/2019 |
| 6 | 06/10/2019 |
| 7 | 08/10/2019 |
and a logs table:
| Task Id | Logged Date |
| 1 | 05/10/2019 |
| 2 | 02/10/2019 |
| 3 | 03/10/2019 |
| 4 | 06/10/2019 |
| 5 | 02/10/2019 |
With the current active relationships if I set logged date filter between 01/10/2019 and 08/10/2019, the logs table will be filtered (in this example it will include all) then will filter tasks table to task ids 1-5. But I would like task table filtering based on created date so in the original date filter will be 1-7. I know I can use USERELATIONSHIP function, in a measure, to activate the inactive relationship between date table and tasks[Task Created Date] column. But is there a way so I do not have to have the inactive filter. So currently I am using:
Hi Baker96 ,
I guess if you want a measure like the following:
Measure = VAR _table = VALUES ( Logs[Logged Date] ) RETURN CALCULATE ( COUNT ( Tasks[Task Id] ), FILTER ( ALL ( Tasks ), Tasks[Created Date] IN _table ) )Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Baker96 ,
Try the measure below:
Measure = VAR mind = MIN ( 'Date'[Date] ) VAR maxd = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNT ( Tasks[Task Id] ), FILTER ( ALL ( Tasks ), 'Tasks'[Created Date] >= mind && 'Tasks'[Created Date] <= maxd ) )Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-joesh-msftThank you for your response, that works well except, my apologies, I missed out something in the logs table which results in the measure not working. The logs mostly likely has duplicate task ids, for example log task 2 on 02/10/2019 but also log again on 03/10/2019. This means there is a one to many relationship betweeen tasks and logs, respectively. This results in the measure not accurately counting, my best assumation it is only counting tasks which have logs in that time frame. (To test I added two extra rows in the logs table:
Task Id Logged Date 2 03/10/2019 4 05/10/2019
