Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Format Statement breaks matrix or table hierarchy

I have a project to create a P&L statement for an Oil and Gas Company.  The P&L is generated from accounting entries that include a non-ragged hierarchy as follows. P&L Rollup Group  P&L Category ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I finally got this to work by changing the filter direction on the 3 xref tables from single to "Both".  In retrospect, this kind of makes sense because

    • Even before any formatting is applied, the Dax expression to calculate the Displayed Amount field depends on attributes of the PLCategoryCode when performing the calculation.  Note this calculation works with both filter direction options ("single" or "both")  regardless of whether or not a format is applied to the amount.
    • In the final solution, the format expression (i.e. Format code) also lives on the PL Category table:  GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])). 

    Note that even applying a hardcoded format to the Displayed amount blew up the display when the filter direction was set to "Single".  I'm not sure why, but apparently this generated a query from CORDetail back to the CORPLCategoryCodes tables which was not supported by the original single relationship.   Setting the relationship to filter in both directions fixed the issue.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I finally got this to work by changing the filter direction on the 3 xref tables from single to "Both".  In retrospect, this kind of makes sense because

  • Even before any formatting is applied, the Dax expression to calculate the Displayed Amount field depends on attributes of the PLCategoryCode when performing the calculation.  Note this calculation works with both filter direction options ("single" or "both")  regardless of whether or not a format is applied to the amount.
  • In the final solution, the format expression (i.e. Format code) also lives on the PL Category table:  GL Amount Display = FORMAT(sum(GLDetail[GL Amount]), Min(PLRollupCategory[FormatCode])). 

Note that even applying a hardcoded format to the Displayed amount blew up the display when the filter direction was set to "Single".  I'm not sure why, but apparently this generated a query from CORDetail back to the CORPLCategoryCodes tables which was not supported by the original single relationship.   Setting the relationship to filter in both directions fixed the issue.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

hi Anonymous 

 

thanks for your solution and detailed explanation,

for my case I was using Format function inside and switch statement. while I was looking for a solution in web, I saw people, used switch statement in an IF statement.  It made sense then I tried to control any matrix cell value is blank or not with and IF statement, actually it worked too. Because dublicated columns has no value, after conrolling with and IF statement, dublicated column gone.

 

happy to solve the problem even if two different solutions.

have a nice week.