Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out values from Measure Iteration

I have a measure that is almost working... I'm stuck on not being able to filter out values that don't match certain criteria.   I'll outline the logic first, then post my current measure: For a ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    mahoneypat thanks for trying to assist and being friendly about it! Since it needed it anyhow I did rebuild the data model and made a few small tweaks. I think though it should've still been able to work in the old data model. The final Measure looks like this, the use of "Values" was the keystone, ironically tripped myslef up on the conditional operators when manually cross checking the mathematical results (oh well):

     

    Avg Cat Deviation Value = 
    VAR __Calc = //create a virtual table that filters to the context of the current row; Site, Project, and associate Space Categories that are Care Services. Include the caluclated measures for the % of Benchmark Program and the previous % of Benchmark Program
     CALCULATETABLE(
     ADDCOLUMNS(
     SUMMARIZE( 'prog Proposed Project Program', Sites[Abbreviated Name], 'prog Projects'[ID], 'prog Space Categories'[ID] ),
     "% of Bnch", [Program % of Cat Benchmark Prog], "Prev %", [Prev % Bnch]
     ),
     ALL('prog Proposed Project Program'), //get all rows from this table
     VALUES(Sites[Abbreviated Name]), //filter to the Site context of current row
     VALUES('prog Projects'[Project Name]), //filter to the Project context of current row
     'prog Proposed Project Program'[Program Area] > 0, //filter for only categories that have changed in this project
     'prog Space Categories'[CareServices] = TRUE() // filter for Space Categories that are only marked as "Care Services"
     )

    VAR AVGBnch = AVERAGEX( __Calc, [% of Bnch]) //calculate the Average of the rows in the filtered table
    VAR AVGPrev = AVERAGEX( __Calc, [Prev %]) //calculate the Average of the rows in the filtered table
    VAR PercRemainder = (AVGBnch - AVGPrev)
    RETURN
     PercRemainder

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Try this measure instead:

 

NewMeasure = calculate(averagex(values(prog_SiteAll[Project Name]), [% of Program Quantity]), prog_SiteAll[CareServices] = "True", Filter(All(prog_SiteAll[Gross Program]), prog_SiteAll[Gross Program]>0))

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mahoneypat thanks for trying to assist and being friendly about it! Since it needed it anyhow I did rebuild the data model and made a few small tweaks. I think though it should've still been able to work in the old data model. The final Measure looks like this, the use of "Values" was the keystone, ironically tripped myslef up on the conditional operators when manually cross checking the mathematical results (oh well):

 

Avg Cat Deviation Value = 
VAR __Calc = //create a virtual table that filters to the context of the current row; Site, Project, and associate Space Categories that are Care Services. Include the caluclated measures for the % of Benchmark Program and the previous % of Benchmark Program
 CALCULATETABLE(
 ADDCOLUMNS(
 SUMMARIZE( 'prog Proposed Project Program', Sites[Abbreviated Name], 'prog Projects'[ID], 'prog Space Categories'[ID] ),
 "% of Bnch", [Program % of Cat Benchmark Prog], "Prev %", [Prev % Bnch]
 ),
 ALL('prog Proposed Project Program'), //get all rows from this table
 VALUES(Sites[Abbreviated Name]), //filter to the Site context of current row
 VALUES('prog Projects'[Project Name]), //filter to the Project context of current row
 'prog Proposed Project Program'[Program Area] > 0, //filter for only categories that have changed in this project
 'prog Space Categories'[CareServices] = TRUE() // filter for Space Categories that are only marked as "Care Services"
 )

VAR AVGBnch = AVERAGEX( __Calc, [% of Bnch]) //calculate the Average of the rows in the filtered table
VAR AVGPrev = AVERAGEX( __Calc, [Prev %]) //calculate the Average of the rows in the filtered table
VAR PercRemainder = (AVGBnch - AVGPrev)
RETURN
 PercRemainder