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
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
Anyone, with a real solution - not created by ChatGPT? 🙂
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.
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:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |