Dynamic P&L and possible relationship issue

DAX DAX Calculations
1

Hello everybody!

I have the following issue:

I followed the Financial reporting w/ power bi course.

I import the accounts of my clients (table Conti) to our erp in order to prepare their financial statements.

I wanted to prepare some visual with power bi in addition to it.

In addition to the Conti table, where my clients data are stored, i have the BilancioFULL table, with the general charter of accounts of my software, and the “PdC Datev” table, that connects my clients’ accounts to the general charter of accounts of my software:
BilancioFULL[Codice] 1–>* PdC Datev[PdC].
PdC Datev[Conto]1–>*Conti[Conto]

(i merged BilancioFULL[Sottocategoria] and PdC Datev[PdC] in ‘Conti’ afterwards just to make experiments with this issue, but won’t work)

rela
rela1750×1347 112 KB

The problem is that i would like that when one of the categories in the P&L are highlighted, in another table should be shown the various accounts in that category. If CE2 stays without relationship, the second table won’t of course filter anything.

2.rel
2.rel1039×592 83 KB

However, if i connect the CE2[Normalized]1–>*Conti[Sottocategoria] table, the subtotals in green in the picture disappear, but i am able to filter the accounts in the other table.

struc
struc780×882 128 KB

highlight
highlight2021×946 125 KB

I think this may happen because the subtotals aren’t really “translated” by the relationship, as they are part of the [normalized] column (which is simply the P&L template “trimmed”) but don’t really exist in PdC Datev[PdC], even though they exist in BilancioFULL…

To circumvent this issue I have tried to set the relationship as non-active so that the 3 subtotals in the measure appear (and they do), plus adding USERELATIONSHIP in the CALCULATE to recreate the deactivated relationship just for the explicit Calculate() in the [Valori CE] measure. however if i then click one of the categories in the P&L table, the accounts won’t be filtered in the second table.

nightfall_0-1619933659517

Do you have any idea on where i am wrong?

PS if i make the relationship active and for the subtotals instead of using the measure i try to put e.g.

CurrentItem = “21) Utile (Perdita) dell’esercizio”, “AAA”

it appears and shows correctly “AAA” so I think it’s something related to the relationship… only, how can I get around it?

nightfall_1-1619933878604

Thank you in advance!

Vittorio
EDNA.pbix (227.1 KB)

2

Hi @nightfall, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Include the masked demo pbix file.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Could it be that something like what’s in this article is happening:
USERELATIONSHIP in calculated columns - SQLBI
??
Namely, that I’d need to get out from the current filter?

4

Simply put, because i am really struggling with this one: given the Switch(True()) structure you give in the course “financial reporting w/ power bi”, where can i find documentation on how to manage to get in another visual matrix table the list of all the accounts forming the category that is equal to the variable CurrentItem when the user clicks on it on the template P&L/BS ??

Thank you again for your patience and sorry if i am stressing you, but i really can’t stand not understanding something :slight_smile:

5

Hi @nightfall! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to promptly especially if there are some learnings to be done.

Bumping this post for more visibility. Thank you for your patience.

6

as a follow up, the solution to this issue was switching the focus from the P&L visual to the other “accounts” table: there using an ad-hoc measure:

IF(
HASONEVALUE(),
CALCULATE()
)
made the trick.

Hope this can help other neophytes like me on this subject.

1 Like