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].
(i merged BilancioFULL[Sottocategoria] and PdC Datev[PdC] in ‘Conti’ afterwards just to make experiments with this issue, but won’t work)
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.
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.
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.
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?
Thank you in advance!
EDNA.pbix (227.1 KB)