Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
As you can see, where the context strictly meets the criteria (Project 2) the measure currently works, but where the criteria are not strictly met, I don't get the "correct" answer.
Here is the current measure:
% Cat Avg =
CALCULATE(
AVERAGEX(
prog_SiteAll,
[% of Program Category]
),
ALLEXCEPT(prog_SiteAll, site_Sites[Abbreviated Name], prog_SiteAll[Project Name]),
FILTER(prog_SiteAll, prog_SiteAll[Gross Program Area] > 0) // I don't think this line is currently doing much, if anything... at least nothing useful.
)
Thanks very much for looking! Spent serveral hours today watching videos and trying to better wrap my head around Evaluations and Filter Contexts particularly when you're in a measure, and I'm stuck!
Solved! Go to Solution.
@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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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 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!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
@Anonymous I think it is pretty unfair to tell me that my model is bad when I've only shared a very small part and I'm dealing with a somewhat complex problem. If you'd care to let me know what part you think is bad, I'd be happy to further articulate what is driving the decisions I'm making and have made.
The ultimate goal here is to flag dependent categories whose percentage of growth is not within a range based on the average of the growth of the independent categories. Meanwhile everything has to be done as running grand totals as the user could enter multiple projects over a 25 year time horizon and they want to see the impacts of their decisions around the size of those projects across each of their sites. Meanwhile there are existing benchmarkr over that 25 year time horizon that the proposed changes need to be compared against and lastly there are no fixed ratios or relationships between the categories of space, rather the point is to do comparitative analysis of possible decisions to determine their feasilibility. Furthermore the whole data source has to be contained within what amounts to a structured excel file and the users have to be able to enter or manipulate the data with relative ease within Excel. Could I further normalize "projects" possibly, but I don't think that would make a difference here, the other columns are also normalized. Should this be done in a full SQL database with straight-up application development, quite possilbe. The simple fact is my mandate is to provide the end users a great deal of flexibility using Excel and PowerBI. I'd be happy to spend an hour or more on Zoom with your taking you through the whole model if you want to offer free consulting services.
@Anonymous
Here is the portion of the model under consideration. With the exception of "Project Name" being able to be normalized I open to suggestions on how else you would further normalize this data model into Facts & Dimensions.
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. 😞
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |