USERRELATIONSHIP not working in my scenario

[Anonymous]

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 -

jobid	master_job_id
1
2
3
4
5
6
7
8
9	2
10	3

Table 2 -

jobid	invoice_source	invoice_type	deleted
2	B		0
4	B		1
1	B	1	0
6	S	0
7	S	1
8	S	1

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- 

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 🙂

[Anonymous · Accepted solution]

@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]

Hi Daxer, 

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

[MattAllington]

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.