Lookupvalue / Calculate value with filters WITHOUT relationship

[RemiAnthonise]

Hi guys,

I have 2 tables in my report. 1 source table called Staffel. I need "Required personnel". The Role in this table is always "VPK".

In my second table, I need the "Required personnel" from my first table. I want to create a way (perhaps lookupvalue or calculate values with filter.) to find the right amount of "required personnel". I (think I) need to combine / match the records of both tables. So I have matching records: start time, stop time, department code, day of the week and VPK. I only need records for role / work type VPK. How can I solve this without having relationships between tables? Besides that: in the table below I don't have a role / work type VPK. I have to make sure that the results below only give me VPK as Role or Work Type.

You can find my sample file here

I hope you guys can help me. I would really appreciate some help. Thanks!

[v-lili6-msft]

hi, @RemiAnthonise 

You may try use this formula to create a column in Beddenplan table

Column 2 = CALCULATE(SUM(Staffel[Required personnel]),FILTER(Staffel,Staffel[Time start]=Beddenplan[Start Tijd]&&Staffel[Time end]=Beddenplan[Stop Time]&&Staffel[Department]=Beddenplan[Department code]&&Staffel[Weekday]=Beddenplan[Day of the week]))

If not your case, please share a smaller file as an example of the issue and expected output.

Best Regards,

Lin

[RemiAnthonise]

Hi @v-lili6-msft , I was wrong with the sampe file I've provided. This one is way smaller and I bit more easy to understand. 

I understand what you did, I see how you can match records from different tables. But, with the measure you gave me I get the error:

A single value for column ‘xxx in table ‘xxx’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I understand the issue but I don't know how to solve it. I've tried something with max() et cetara but that doesn't solve the trick. If you're willing to help, please!

Cheers,

Remi.

[v-lili6-msft]

Hi, @RemiAnthonise 

This formula is to create a Column not a measure.

You could use the same logic to adjust it.

Best Regards,

Lin

[RemiAnthonise]

@v-lili6-msft  , please see below for my results. In my sample file you'll find these tables on page 1. I expect the created measure to show me '6' as outcome.

Maybe it has something to do with the relationship between these tables. I've created an unique field in the Staffel by merging department, start time, stop time, beds open and weekday to one unique field.

Sample file here.

Thanks a lot!

[RemiAnthonise]

Hi @v-lili6-msft , 

If you have time to check my sample file, please. I would appreciate it.

[RemiAnthonise]

Hi @v-lili6-msft , 

I'm sorry to bother you with this issue (again), but if you have time to help me with this one, please.

Thanks,

Remi.

[RemiAnthonise]

Hi @v-lili6-msft . Sorry, I misread.

I get some strange results, see image below, but that must have something to do with relationships between these tables. I'll fix that. When this works, I'll let you know. Thanks!