Forum Discussion
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 ...
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
Good dimensional models consist of facts and dimensions. What you have shown so far does not adhere to this.
You are mixing many different entities in one table. This is BAD and there's nothing more to say. Hence your DAX is not easy to write.
Please watch the vids I've given you links to.
Best
D
You are mixing many different entities in one table. This is BAD and there's nothing more to say. Hence your DAX is not easy to write.
Please watch the vids I've given you links to.
Best
D
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 can immediately see flaws in this design. Fact tables are measurements about processes (with the exception of factless fact tables). All their columns should be hidden. Only measures can be exposed. Slicing must be done only via dimensions.
All columns in your fact table are exposed. BAD.
Define your business entities. Extract them into dimensions where each row is a description of one unique item. Your fact table should consist only of keys to dimensions and measurements. All of this must be hidden. Please, just rethink this design. Make it CLEAN. Then we can think about simple and fast measures.
Best
D