Help needed with relations between tables (sample file included)

[Carla17]

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 dashboard, but I'm stuck.

So I have a table with the shoots: date, hospital, photgrapher, shoot number.

I have another table with: photographer, hospital (this table is to define in which hospital the photographers do shoots).

I can easily get the number of shoots of a photographer and the number of shoots per hospital. But we want to know how many shoot a photographer has done on the total of shoots for the hospitals he was assigned to.

So something like this. It would be awesome to also have a time dimension. So the number of shoots and possible shoots in the last 3 months, this year,...

Photographer	Number of shoots	Number of possible shoots
Photographer 1	3	7
Photographer 10	2	7
Photographer 11	2	74
Photographer 12	1	8
Photographer 13	1	19
Photographer 14	3	104
Photographer 15	9	104
Photographer 16	6	21

I made a sample file pbix with the data model. You can download it by Wetransfer: https://we.tl/t-O7jrYEiGTp (corrected version, so without a duplicate in the taglist)

All help is welcome,

Thank you very much!

Carla

[v-weiyan1-msft]

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]

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:

Photographer	Numer of shoots done	Number of possible shoots
Photographer 11	2	74

Do you think you can crack this for my?

Thank you very much,

Carla

[Fowmy]

@Carla17 

There are duplicates in the Taglist, please check.

[Carla17]

Oh, I'm really sorry. You are 100% right. This is the corrected file: https://we.tl/t-CbrMSIDkjR. I double checked, this was the only duplicate.

Thank you very much for pointing this out to me. 🙏

.