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

USERRELATIONSHIP not working in my scenario

 

This is a very specific scenario that I need help with but would greatly appreciate any feedback. 

Below is a sample of that dataset - 

 

Table 1 -

 

jobidmaster_job_id
1 
2 
3 
4 
5 
6 
7 
8 
92
103

 

Table 2 -

 

jobidinvoice_sourceinvoice_typedeleted
2B 0
4B 1
1B10
6S0 
7S1 
8S1 

 

Active Relationship - Table 1 'jobid' to Table 2 'jobid'

Inactive Relationship - Table 1 'master_job_id' to Table 2 'jobid'

 

I have created a DAX to lookup values on table 2 from table 1 based on certain criteria. Below is the DAX 

Invoiced_Yes/No =
IF (
CALCULATE (
COUNT ( 'jobs'[jobid] ),
FILTER ( invoices, invoices[deleted] ="0" && invoices[invoice_source]="B" && COUNT ( invoices[jobid] ) > 0 )
)
= BLANK (),
0,
1
)

 

This returns values that I need accurately based on the active relationship (jobid to jobid). But I need another DAX lookup the values based on the inactive relationship (Table 1 'master_job_id' to Table 2 'jobid'). The DAX is have used is - 

 

Master_Invoiced_Yes/No =
IF (
CALCULATE (
COUNT ( jobs[master_job_id] ),
FILTER ( invoices, invoices[deleted] ="0" && invoices[invoice_source]="B" && COUNT ( invoices[jobid] ) > 0 ),USERELATIONSHIP(jobs[master_job_id],invoices[jobid])
)
= BLANK (),
0,
1
)

 

I have used the USERELATIONSHIP function to invoke the inactive relationship but this doesn't seem to be working the way I need. Below is the output of the measures- 

 

omarbaig_1-1617259128373.png

 

In the image above the measure Invoiced Yes/No is showing the corect data. On the other hand, the Master Invoiced Yes/No is incorrect. It should be showing 1 on master_job_id 2,3. 

 

Link to PBIX file - https://gofile.io/d/xRnB49 

 

Hope this makes sense. Would greatly appreciate any help 🙂

 

 

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

@omarbaig 

 

First of all, you can't use USERELATIONSHIP in this model (there are reasons I won't go into because I'd have to explain the theory and I don't have time). Romove the inactive relationship from the model.

 

Here's the first measure:

 

 

Been Invoiced = 
if( HASONEVALUE( Jobs[jobid] ),
    1 - ISEMPTY(
        FILTER(
            Invoices,
            Invoices[deleted] = 0 &&
            Invoices[invoice_source] = "B"
        )
    )
)

 

 

Here's the second one:

 

 

Master Been Invoiced = 
if( HASONEVALUE( Jobs[jobid] ),
    var MasterJobID = SELECTEDVALUE( Jobs[master_job_id] )
    var BeenInvoiced =
        CALCULATE(
            [Been Invoiced],
            REMOVEFILTERS( Jobs ),
            Jobs[jobid] = MasterJobID
        )
    return
        BeenInvoiced
)

The second measure even informs you whether the job has or not a master job (BLANK means no master job).

 

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

@omarbaig 

 

First of all, you can't use USERELATIONSHIP in this model (there are reasons I won't go into because I'd have to explain the theory and I don't have time). Romove the inactive relationship from the model.

 

Here's the first measure:

 

 

Been Invoiced = 
if( HASONEVALUE( Jobs[jobid] ),
    1 - ISEMPTY(
        FILTER(
            Invoices,
            Invoices[deleted] = 0 &&
            Invoices[invoice_source] = "B"
        )
    )
)

 

 

Here's the second one:

 

 

Master Been Invoiced = 
if( HASONEVALUE( Jobs[jobid] ),
    var MasterJobID = SELECTEDVALUE( Jobs[master_job_id] )
    var BeenInvoiced =
        CALCULATE(
            [Been Invoiced],
            REMOVEFILTERS( Jobs ),
            Jobs[jobid] = MasterJobID
        )
    return
        BeenInvoiced
)

The second measure even informs you whether the job has or not a master job (BLANK means no master job).

 

View solution in original post

omarbaig
Frequent Visitor

Hi Daxer, 

 

This works perfectly. You were of great help, thanks for your feedback! 🙂

To me, this looks like data loaded from a relational database that has not been prepared well enough for a reporting data base. The table structure used for a rdbms is not the same as what is needed for reporting. It is difficult to say exactly how the data should be prepared because I don't understand your data. You need to think about the business context of the data and build an entity based model (star schema if possible). I can see the concept of jobs and master jobs. This suggests to me there are 2 levels of jobs. Some jobs roll up to master jobs. It's clear to me that job 2 and 3 are master jobs, but what about 1?  I think this is a master job too. And what about 9 and 10?  These seem like sub jobs. My best guess is you should build a header detail model. One table with master jobs and a second table with sub jobs linked back to the master jobs. Each job should be in one table or the other.   Then you need to understand the invoices. Are they always at the mast job level?    Can there be more than 1 per job?

 

lots to think about. I can guarantee that once you work out the correct model, the DAX will be easier. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors