Forum Discussion

Roy_W's avatar
Roy_W
Frequent Visitor
5 years ago
Solved

Filter the value from another table that cannot create relationship

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 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
      Roy_W
      Frequent Visitor
      5 years ago

      parry2k , thanks for the response.

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

       

      Thanks

       

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

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

  • niteshtrehan89's avatar
    niteshtrehan89
    Helper III
    5 years ago

    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"})