Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
pbiXlsx
Frequent Visitor

Parent Child Hierarchy plus fact sheet details

Hi, 

 

i am trying to solve the following problem:

 

I used the Dax Pattern of Parent-Child Hierarchy by the Italians.

 

It works great, however, as soon as i add a column from my fact sheet (called "posting text") as category in my visual my measure breaks. Can anyone help how to adjust my measures?

 

 

 

[SumAmount] = 
VAR SumAmt = SUM(FactSheet[Amount])
VAR Result = IF(SumAmt = 0, BLANK(), SumAmt) 
Return
    Result

[AccountEntityBrowseDepth GB View] = 
ISINSCOPE( DimAccount_full[Level3GBView] ) 
+ ISINSCOPE( DimAccount_full[Level4GBView] ) 
+ ISINSCOPE( DimAccount_full[Level5GBView] )
+ ISINSCOPE(DimAccount_full[Level6GBView] )
+ ISINSCOPE( DimAccount_full[Level7GBView] )

[AccountEntityRowDepth] = MAX(DimAccount_full[PathDepth])-2 
//minus 2 because i do not show the first 2 levels within my visual



VAR Val = [SumAmount]
VAR EntityShowRow = [AccountEntityBrowseDepth GB View] <= [AccountEntityRowDepth] 
VAR Result = 
    IF(EntityShowRow, Val)
Return
    Result

 

4 REPLIES 4
pbiXlsx
Frequent Visitor

Anyone, with a real solution - not created by ChatGPT? 🙂

pbiXlsx
Frequent Visitor

ChatGPT should be used with caution 🙂 This does not work

try this one

When you add the "posting text" column to your visual, it might be affecting the context in which the measures are being evaluated, causing them to break. One way to address this issue is by modifying your measures to account for the changes in the context when the "posting text" column is included.

To do this, try adjusting your [AccountEntityRowDepth] measure and the other measures accordingly. Here's the modified code:

[SumAmount] = VAR SumAmt = SUM(FactSheet[Amount]) VAR Result = IF(SumAmt = 0, BLANK(), SumAmt) Return Result [AccountEntityBrowseDepth GB View] = ISINSCOPE(DimAccount_full[Level3GBView]) + ISINSCOPE(DimAccount_full[Level4GBView]) + ISINSCOPE(DimAccount_full[Level5GBView]) + ISINSCOPE(DimAccount_full[Level6GBView]) + ISINSCOPE(DimAccount_full[Level7GBView]) [AccountEntityRowDepth] = MAXX( FILTER( ALLSELECTED(DimAccount_full), NOT(ISBLANK(FactSheet[Amount])) ), DimAccount_full[PathDepth] ) - 2 VAR Val = [SumAmount] VAR EntityShowRow = [AccountEntityBrowseDepth GB View] <= [AccountEntityRowDepth] VAR Result = IF(EntityShowRow, Val) Return Result

In this modified code, I changed the [AccountEntityRowDepth] measure to calculate the maximum path depth for the selected accounts, filtering out the rows with blank amounts. This way, the measure should work correctly when the "posting text" column is added to the visual.

Test the modified measures in your report, and they should now work as expected when you include the "posting text" column in your visual. If you still encounter issues, please provide more context or share a sample of your data model so I can better understand the problem and provide more tailored assistance.

mohdasaad94
Helper I
Helper I

t's possible that the addition of the "posting text" column from your fact sheet is causing the measure to break because it is not part of the hierarchy. You may need to adjust your measures to account for this.

One solution is to use the "posting text" column as a filter rather than a category in your visual. This will allow you to use the parent-child hierarchy without interfering with the measure calculation.

Here is an example of how you can adjust your measures:

 

Here is an example of how you can adjust your measures:

 

scss
[SumAmount] = VAR SumAmt = SUM(FactSheet[Amount]) VAR Result = IF(SumAmt = 0, BLANK(), SumAmt) Return Result [AccountEntityBrowseDepth GB View] = ISINSCOPE( DimAccount_full[Level3GBView] ) + ISINSCOPE( DimAccount_full[Level4GBView] ) + ISINSCOPE( DimAccount_full[Level5GBView] ) + ISINSCOPE(DimAccount_full[Level6GBView] ) + ISINSCOPE( DimAccount_full[Level7GBView] ) [AccountEntityRowDepth] = MAX(DimAccount_full[PathDepth])-2 //minus 2 because i do not show the first 2 levels within my visual [PostingTextFilter] = IF(HASONEVALUE(FactSheet[Posting Text]), FactSheet[Posting Text], BLANK()) VAR Val = [SumAmount] VAR EntityShowRow = [AccountEntityBrowseDepth GB View] <= [AccountEntityRowDepth] VAR Result = IF(EntityShowRow, Val) Return Result // This measure uses the PostingTextFilter as a filter [SumAmount with Posting Text Filter] = VAR SumAmt = CALCULATE(SUM(FactSheet[Amount]), [PostingTextFilter]) VAR Result = IF(SumAmt = 0, BLANK(), SumAmt) Return Result
 

With this adjustment, you can use the "SumAmount with Posting Text Filter" measure in your visual when you want to include the "posting text" column. This measure uses the "PostingTextFilter" as a filter, so it will not interfere with the parent-child hierarchy.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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