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!
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.
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!
This formula is to create a Column not a measure.
You could use the same logic to adjust it.
@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.
Thanks a lot!
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.
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!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.
Join our monthly meetings and learning sessions.
We are excited to announce the Power BI Super Users!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.