Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Baker96
Frequent Visitor

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 IdCreated Date
101/10/2019
202/10/2019
303/10/2019
404/10/2019
502/10/2019
606/10/2019
708/10/2019

 

 

and a logs table:

Task IdLogged Date
105/10/2019
202/10/2019
303/10/2019
406/10/2019
502/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:

Count Task Created = CALCULATE(COUNT(Tasks[Task Id]) ,USERELATIONSHIP(Tasks[Task Created Date], 'Logged Date'[Date]))
 
But is there a function instead of USERELATIONSHIP so I do not have to have inactive relationships in the model?

 

 

 

 

1 ACCEPTED SOLUTION

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYLZpPBEzI5HoqhN7l...

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.

View solution in original post

4 REPLIES 4
Baker96
Frequent Visitor

Example Power BI relationship.jpg

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZs-fh_wm9NAlubMhm... 

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 IdLogged Date
203/10/2019
405/10/2019

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYLZpPBEzI5HoqhN7l...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.