DAX to filter values based on other tables

[Anonymous]

Hi,

I would need advice on how to create pivottable, based on inputs from several tables.

1. First I have an account masterdata table

This table defines characteristics of each account, and if the account data is needed to be detailed on product group level OR summed to unit level (one or another, not both)

2. Second I have Balance sheet (BS) table, which roughly looks like this

3. Third I have similar table for P&L values

BS codes may appear here as well

4. Fourth I have a table, which will list all the Units and their product groups, like that

End goal?
Pivot, which will:

-be based on the account list table (meaning, that every account in the list must be present in the table, value or no value)
-Based on account setting, must pick correct value from correct BS or PL table (in this exmple, if account 1010 is balance sheet account, then it must ignore the account value in P&L table and pick the value from BS table)
-If its determined which table is correct, it must check if account value needs to be summed across the PG-s the unit has OR repeat the account for every PG there is in the list. Meaning, if account is Unit total, then it cannot display every unit PG individually
-Of course, account table specifies the sign, so if -1 is present, that account needs to be with opposite sign.

An Pivot example based on the tables below

I hope my reasoning was understandable and doable with DAX, with so many filters...

[lbendlin]

Please show a sanitized version of your data model. 

[Anonymous]

Hi, Yes, its probably the best way, I created an example logic here

https://easyupload.io/ve0fq6

Thanks for review