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 my sample file here. Please see attached image 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, see image below.

I've created an unique field in the tables Beds and Required Beds by merging department, start time, stop time, beds open and weekday to one unique field.

On page 1, you'll find results of both tables. On page 2, I tried to explain what I expect.

Example: on a certain department, with corresponding time start / end and weekday, I have 'beds open'. Let's say 22. In the second table, we can find what the required amount of personnel is. So I expect table 1 to give me the corresponding number of personnel, in stead of the 3256 (example) I get now. 

Column 2 from table 'Beds' is wrong and I need help with that. I hope this is clear.

[jdbuchanan71]

@RemiAnthonise 

Would it be possible for you to summarize the Beds table?  Instead of sending in a row for every open bed send in a row with the sum of open beds.  This would help with the calc and shrink the size of the table.  Looking at that data I didn't see any reason to have a row for each bed.

[RemiAnthonise]

Hi @jdbuchanan71 ,

Many thanks for your reply. I guess this will solve my problem. The only thing is: I don't get the same results as you showed me. Did you do anything else, like editting relationships, modified something for the date or what so ever?

I made the measure for Beds Open and I literally copied the measure for Required Personnel. 

Thanks, cheers.

[jdbuchanan71]

@RemiAnthonise 

Sorry, you have to delete the relationship between the tables.  It is not pointing to the row you want anway.  If you were able get the summarized table then the relationship would have worked for you.

[jdbuchanan71]

Hello @RemiAnthonise 

One problem you are running into is the [Count of open beds] is just a count of the rows in the table.  The model doesn't have it available for your lookup so we will need a measure for that.

Beds Open Measure = COUNT ( Beds[Beds open] )

Then we can write a measure that uses that amount plus the DEPT, Start Time and Day of Week to get the amount.

Required Personnel Measure = 
CALCULATE(
    MAX ( 'Required beds'[Required personnel] ),
    FILTER ( 'Required beds', 'Required beds'[Beds open] = [Beds Open Measure] ),
    TREATAS ( VALUES ( Beds[Department code] ), 'Required beds'[Department] ),
    TREATAS ( VALUES ( Beds[Start Tijd] ), 'Required beds'[Time start] ),
    TREATAS ( VALUES ( Beds[Day of the week] ), 'Required beds'[Weekday] )
)