Access variable within IF statement from a non summarized field

[Anonymous]

The logic of the query is as follow:

SummaHours = 
CALCULATETABLE(
    SUMMARIZE(
        'RESBOOK (2)',
        'RESBOOK (2)'[Name],
        "3M", SUM('RESBOOK (2)'[3M]),
        "3MH", IF('RESBOOK (2)'[Priority] IN {"Audit", "Reporting"}, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],TODAY(),edate(TODAY(),3)), Calndar[WD] == "True")*5.6, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],TODAY(),edate(TODAY(),3)), Calndar[WD] == "True")*7),
        "6M", SUM('RESBOOK (2)'[6M]),
        "12M", SUM('RESBOOK (2)'[12M]),
        "18M", SUM('RESBOOK (2)' [18M]),
        "6MH", IF('RESBOOK (2)'[Priority] IN {"Audit", "Reporting"}, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),3),edate(TODAY(),6)), Calndar[WD] == "True")*5.6, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),3),edate(TODAY(),6)), Calndar[WD] == "True")*7),
        "12MH", IF('RESBOOK (2)'[Priority] IN {"Audit", "Reporting"}, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),6),edate(TODAY(),12)), Calndar[WD] == "True")*5.6, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),6),edate(TODAY(),12)), Calndar[WD] == "True")*7),
        "18MH", IF('RESBOOK (2)'[Priority] IN {"Audit", "Reporting"}, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),12),edate(TODAY(),18)), Calndar[WD] == "True")*5.6, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],edate(TODAY(),12),edate(TODAY(),18)), Calndar[WD] == "True")*7)
    ),
    'RESBOOK (2)'[Name]<>""
)

The bit that does not work is the if statement as the field 'RESBOOK (2)'[Priority] is not recognised at all in this context.

I can't add it to the summarization context as it will create duplicates in rows.
Is there a way to access that value for filtering purposes only?

[rubayatyasmin]

Hi, @Anonymous 

you need to use calculate with filter. for example,

"3MH", IF(
CALCULATE(
MAX('RESBOOK (2)'[Priority]),
FILTER(
'RESBOOK (2)',
'RESBOOK (2)'[Name] = EARLIER('RESBOOK (2)'[Name])
)
) IN {"Audit", "Reporting"},
CALCULATE(
COUNTROWS(Calndar),
DATESBETWEEN(Calndar[Dates], TODAY(), EDATE(TODAY(), 3)),
Calndar[WD] == "True"
) * 5.6,
CALCULATE(
COUNTROWS(Calndar),
DATESBETWEEN(Calndar[Dates], EDATE(TODAY(), 3), EDATE(TODAY(), 6)),
Calndar[WD] == "True"
) * 7
),

In this modification, CALCULATE changes the context of evaluation to each row of 'RESBOOK (2)' where the [Name] matches the current row's [Name] in the SUMMARIZE table. We then take the MAX of [Priority] under this context, which should return the [Priority] value of the row in the original table that corresponds to the current row in the SUMMARIZE table. This value is then used in the IF function.

Remember to replace this with all your IF statements.

If my assistance helped you in any way, hit 👍. 

[Anonymous]

Thank you for your reply, before testing this I have to ask if the MAX('RESBOOK (2)'[Priority]) would work with String. Being the 'RESBOOK (2)'[Priority] a text variable. Is MAX used to access the variable only or it has function in this case?
Many thanks

[rubayatyasmin · Accepted solution]

Good catch. MAX is used here to make sure we always have a single value to use in our IF statement, even when our context might have multiple rows. We can use lookup value. see if this helps.

"3MH", IF(LOOKUPVALUE('RESBOOK (2)'[Priority],'RESBOOK (2)'[Name],'RESBOOK (2)'[Name]) IN {"Audit", "Reporting"}, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],TODAY(),edate(TODAY(),3)), Calndar[WD] == "True")*5.6, CALCULATE(COUNTROWS(Calndar), DATESBETWEEN(Calndar[Dates ],TODAY(),edate(TODAY(),3)), Calndar[WD] == "True")*7),

if my assistance helps you in any way, hit 👍.