Measures and accessing the "outside" from a row context.

[trevb]

Okay so I am writing a measure and as part of that I have a situation where I have calculated a total for a "Programme" row that includes all the sub-projects of that programme.

If the current context includes a programme and one of its sub project then I want it to include the value for the programme in the total and ignore the value for the sub project.  The following pseudo code shows what I want to achieve.

sumx(Projects,

        switch(Projects[ProjectType]),

                 "Programme", .........stuff to return a sum of all of the sub-projects,

                 "SubProject", IF this projects parent is in the summation then 0  otherewise SubProject value,

                 [ProjectValue]

        )

)

Clearly at the point I am doing the red stuff I am in row context and thus only know about one set of values.  My challenge is that I want to refer back to the overall context.  Like most I initially assumed earlier would help here however I am after the original query context not a parent row context.

I want to be able to say something like if(Contains(EarlierValues,EarlierValues[ProjectID],[MyID]),......blah.  I can do something to pull out all "SubProjects" by doing

var HasSubs = CONTAINS(values(Projects[ProjectType]),[ProjectType],"SubProject") return

Then doing an if(HasSubs,0,[SubProjectValue]).  This works but ignores a situation where a programme is in the query data and this is a sub project but they are not related.  In that case I would want to see the value for both the programme and the subproject in the total.

My c# tuned brain is probably overcomplicating this and someone can probably point out something quite simple that will achive this but I've run aground now so thought I'd shout out to you again.

[v-shex-msft]

Hi trevb,

Based on your description, I create a sample, you can follow below steps:

1. Create test table.

 

2. Write a measure to calculate the subtotal.

Dax of Measure:

Sum of sub porject = var uid= LOOKUPVALUE(TestTable[UID],TestTable[ParentCode],BLANK(),TestTable[UID],VALUES(TestTable[UID])) return

CALCULATE(SUMX(FILTER(TestTable,TestTable[ParentCode]=uid),TestTable[ProjectValue]),ALL(TestTable))

3. Add a calculate column to display the specify data.

Dax of Calculate column:

SubTotal = SWITCH([ProjectType],

"Programme",[Sum of sub porject],

"SubProject",if(ISBLANK(TestTable[ParentCode]),[ProjectValue],0),

[ProjectValue])

 

If above is not help, please provide your table structure with some sample data and feel free to let me know.

Regards,

Xiaoxin Sheng

[trevb · Accepted solution]

谢谢Xiaoxin,

In the end I sat down and had a real think through the problem with my increasing understanding of context.  Finally I came up with a DAX query that did not need this and was about 20 times faster than what I'd had in place to that point.  Many thanks for giving it a go for me.

[v-shex-msft]

Hi @trevb,

Has this solution worked?

Regards,

Xiaoxin Sheng

[trevb]

Yes rewriting my query worked.