Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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('RESBOO...
"CALCULATE"
"CALCULATETABLE"
"DAX"
SUMMARIZE
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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 👍. 

     

rubayatyasmin's avatar
rubayatyasmin
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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 👍.