cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Lookupvalue / Calculate value with filters WITHOUT relationship

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!

lookup.jpg

7 REPLIES 7
Community Support
Community Support

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

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

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.

Hi, @RemiAnthonise 

This formula is to create a Column not a measure.

You could use the same logic to adjust it.

 

Best Regards,

Lin

 

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

@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!

 

lookup.jpg

Hi @v-lili6-msft , 

 

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

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.

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!

relation.jpg

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors