Filter the value from another table that cannot create relationship

[Roy_W]

Hi,

I want to create a bar chart to display IDs with their respective total hours.

Then I need to apply one filter value from another table that only exists indirect relationship between them.

I cannot create the relationship since there is no distinct value, please helps to check if any magic function can fulfill the requirment.

I have 4 tables format likes below:

The relationship between these tables:

I can create below result based on table "from_id history":

The disred result after applying status="Open" from table "to_id data":

Attached the working file, thanks.

working files 

[niteshtrehan89]

Please try teh below measure.

sum= calculate(SUM ( 'from_id history'[hours] ),

CROSSFILTER ( 'from_id relationship'[from_id], 'from_id data'[from_id], Both ),

    'to_id data'[status] in {"Open","Complete"})

[parry2k · Accepted solution]

@Roy_W your original question was to show open and that's why. It is better to be specific. Anyhow, you can remove this open condition from the measure and then add a slicer and user can choose whatever they want

hours sum = 
CALCULATE ( 
    SUM ( 'from_id history'[hours] ), 
    CROSSFILTER ( 'from_id relationship'[from_id], 'from_id data'[from_id], Both )
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Roy_W here is the measure and use this in the visual:

hours sum = 
CALCULATE ( 
    SUM ( 'from_id history'[hours] ), 
    CROSSFILTER ( 'from_id relationship'[from_id], 'from_id data'[from_id], Both ), 
    'to_id data'[status] = "Open" 
) 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Roy_W]

@parry2k , thanks for the response.

This method can only filter the "Open" case, any idea that can let user choose "Open" or "Complete".

Thanks