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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@Anonymous 

 

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
Anonymous
Not applicable

@Anonymous 

 

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

 

Anonymous
Not applicable

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors