Forum Discussion

Carla17's avatar
Carla17
Frequent Visitor
2 years ago

Help needed with relations between tables (sample file included)

Hi experts,   I'm a voluteer in an organisation that does shoots in hospitals from premature babies. So the parents have professional pictures of the babies. I'm helping them out making a power bi ...
Need Help
v-weiyan1-msft's avatar
v-weiyan1-msft
Community Support
2 years ago

Hi Carla17 ,

 

Based on the sample and description you provided, Please try code as below to create a measure.

Number of shoots = 
VAR _NUM1 = CALCULATE(
                    COUNTROWS('Shoots'),
                    FILTER('Shoots',
                    'Shoots'[Hospital] = MAX('Shoots'[Hospital]))
                    )
VAR _NUM2 = CALCULATE(
                    COUNTROWS('Shoots'),
                    ALLEXCEPT('Shoots','Shoots'[Photographer])
                    )
return IF(ISINSCOPE('Shoots'[Hospital]),_NUM1,_NUM2)

Result is as below.

Regarding your reference to Number of possible shoots, I'm not sure what calculations you are basing your prediction on. If this reply still does not help solve your problem, please feel free to ask us.

 

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Carla17's avatar
Carla17
Frequent Visitor
2 years ago

Hi Yulia,

 

Thank you very much for replying to my post.

But unfortunally this is not what I was looking for.

 

I try to get the use case a little more clear.

  • A photographer is attached to 1 or more hospitals.
  • So every hospital has between 10 and 20 photograhers that can do shoots for that hospital (this is the table "Taglist" in the model that defines de link between the hospital and the photographer)
  • When there is a call, the photographers attached to the hospital can let the organisation know he is availble to do the shoot. 1 photographer is assigned for the shoot. This is in the table "Shoots".
  • We work with volunteers, but we ask them to do a minimum of 5 shoots a year. Reason: there is insurrance to pay etc.
  • What I want to know is: how many shoots a photographer does on the total of call's there have been for the hospitals he was assigned to.

eg for the datamodel: 

  • Photographer 11 has done 2 shoots.
  • Photographer 11 is linked to hospital 1, 4 and 9. 
  • In total there have been 74 calls 
    • 41 call's for hospital 1
    • 17 calls for hospital 4
    • 16 calls for hospital 9

So I would need a table:

PhotographerNumer of shoots doneNumber of possible shoots
Photographer 11       274

 

Do you think you can crack this for my?

 

Thank you very much,

 

Carla