cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Sum higher level in hierarchy based on measure on lower hierarchy

DimReport

 Report ReportRow Account SignType UniqueKey Rapport129 129/* CM 35100 1 4177960 Rapport129 129/* CM 35100 -1 4174475 Rapport129 129/* CM 34000 1 4176966 Rapport129 129/* CM 93220 -1 4179774

Bridge

 UniqueKey Account 4179774 93220 4177960 35100 4176966 34000 4174475 35100

Fact

 FactKey Account Amount Sum(Sign) Amount*Sign 1 35100 10 0 0 2 35100 20 0 0 3 34000 100 1 100 4 34000 200 1 200 5 93220 -500 -1 500 6 93220 300 -1 -300 130 500

I have placed the measures here to show how it´s related to rows in Fact.

Sum(Sign) is the sum of Sign from DimReport

Model

I can´t use RELATED to get the corresponding sign into the fact table since one account can have multiple signs ( see Account 35100 )

I have a hierarchy with 3 levels in a dimension.

"129/*CM" is second level and third level is Account

Account can have a Sign with 1 or -1 and if we are standing on level 3 I want to multiply Amount with Sign.

Amount*Sign:= IF(HASONEFILTER(DimRapport[Account]),[SumAmount]*[Sign], [SumAmount])

SumAmount := sum(Amount)

I now want the sum on level 2 (129/*CM) to sum up the Amount*Sign values, not the SumAmount values. But my obstacle is that in my Amount*Sign measure I have said that level 3(Account) has to be filtered.

I should mention that this have been solved previously in MDX with CALCULATIONPASSVALUE but my knowledge of MDX is zero, and i can´t figure out how this can be translated into DAX.

MDX code:

Scope(

{

[MEASURES].[SumAmount]

}

);

this

=

case [DimReport].[DimReport].Currentmember.level.ordinal

when 3 then

CALCULATIONPASSVALUE([Measures].Currentmember * [DimReport].[DimReport].currentmember.Properties( "Sign" ),4 )

else

CALCULATIONPASSVALUE([Measures].Currentmember ,4)

end;

End Scope;

How can I get the sum on level 2 to sum up the values from Amount*Sign? The sum should be

500 and not 130

4 REPLIES 4
Super User

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Regular Visitor

I get the HTML error though and i have tried to post a new question as described in link but i still get the headers wrong. Is it possible to view the data anyhow?

Super User

Doesn't look like the bridge table is required?

Regular Visitor

This is an old existing solution in multidim cube so i dont have all background, but i agree that maybe it would be same result if we just had a relation directly between fact and dimreport.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors