Forum Discussion
Filter out values from Measure Iteration
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
Microsoft EmployeeTry 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
mahoneypat thanks for trying, but that did not work either. The filtering clearly works, but I end up with the row value in the Measure. So for the ambulator row where the % of Program Category is 50.85% the result in the measure is the same value. Rows that don't meet the logic criteria show blank, so that is "good" in the sense that the logic is working, but not in the context of row that are not compliant, which means it is not getting out of the row context to iterate over multiple values.
In a previous iteration I had gone down the road of a virtual table, which I believe may still be where the solution lies, so I may try applying what you gave me to a virtual table, and see if that propegates as expected. Below is the code as I wrote it, there are a couple of relationships not readily apparent in my original post, though I don't think they have any bearing on the performance of this particular measure.
Measure =
CALCULATE(
AVERAGEX(
VALUES(
prog_SiteAll[Project Name]
),
[% of Program Category]
),
prog_Categories[CareServices] = TRUE(),
FILTER(
ALL(prog_SiteAll[Gross Program Area]),
prog_SiteAll[Gross Program Area] > 0
)
)
Thanks for trying!
- mahoneypat
Reread your post. Instead of iterate over Program Name, inside the Values() of my proposed measure try the Cat column instead.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
I assume you meant Project Name and not Program Name since that is the AVGX iteration.
Regardless, still no dice. Returns the same result as the row, rather than iterating over all the rows that meet the criteria. 😞
- mahoneypat
Ok. Let's try the brute force approach.
NewMeasure = var result = calculate(averagex(values(prog_SiteAll[Cat]), [% of Program Quantity]), prog_SiteAll[CareServices] = "True", Filter(All(prog_SiteAll[Gross Program]), prog_SiteAll[Gross Program]>0))
return if(and(min(prog_SiteAll[Gross Program])>0, min(prog_SiteAll[CareServices])="True"), result, blank())
I think Cat in the Values, but sub that out if not right.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat you did get me a step closer! So this might work. If I take the Category out of the visual, it does properly average the valid values! So, now I'm into the space of why does the measure work in one context and not another. I haven't previously gotten to even work when the visual was simplified.
- You are struggling because your model is... well, bad. If you reshape it so that it meets the rules of correct dimensional modelling, your measure will be very simple to write and fast. Currently, the model is simply non-workable.
Here is something about good models in PBI:
https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA
Best
D