Forum Discussion
Access variable within IF statement from a non summarized field
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?
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 👍.
3 Replies
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 👍.
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 👍.