cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trevb Member
Member

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
trevb Member
Member

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

谢谢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.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

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

Hi trevb,

 

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

 

1. Create test table.

Capture.PNG 

 

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])

 

 Capture2.PNG

 

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
trevb Member
Member

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

谢谢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.

View solution in original post

Community Support Team
Community Support Team

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

Hi @trevb,

 

Has this solution worked?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
trevb Member
Member

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

Yes rewriting my query worked.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 277 members 3,194 guests
Please welcome our newest community members: