Dynamic conditional usage of dimension tables

[dhannaa]

Hi all,

I'm trying find the best solution for filtering accounting data based on different custom groupings.

I currenly have this kind of parameter table for groupings. 

And these dimension tables to tell how to read accounting data into those groups.

And below is how the grouping of the data should happen.

What would be the best way to find the total amount for each individual group? As there should be no usage of filtering table if selected group has zero rows for the dimension table in question. For example, as Group1 uses only account numbers, it should ignore the dimension tables for cost centers and Grouping DimX.

Thanks in advance, I'm a bit stuck as my ideas don't seem to work as I want.

Grouping
Groupd ID	Group
1	Group1	Filtered only by Accounts
2	Group2	Filtered by Accounts And Cost Centers
3	Group3	Filtered by Accounts And Cost Centers
4	Group4	Filtered by Accounts and Dimensions

Grouping Account
Account	Grouping
5000	1
5001	1
5002	1
5003	1
5004	1
5005	2
5006	2
5007	2
5005	3
5006	3
5007	3
5010	4
5011	4
5012	4

Grouping Cost Centers
CostCenter	Grouping
1	2
2	3

Grouping DimX
Dimension	Grouping
1	4

Accounting Data
Account	CostCenter	DimX	Amount
5000	1	1	50,00 €	Group1
5001	2	2	100,00 €	Group1
5002	3	2	200,00 €	Group1
5003	4	2	300,00 €	Group1
5004	5	1	50,00 €	Group1
5005	1	2	100,00 €	Group2
5006	1	3	200,00 €	Group2
5007	1	4	300,00 €	Group2
5005	2	5	300,00 €	Group3
5006	2	6	50,00 €	Group3
5007	2	3	100,00 €	Group3
5010	12	1	200,00 €	Group4
5011	13	1	300,00 €	Group4
5012	14	1	50,00 €	Group4

[v-rongtiep-msft]

Hi @dhannaa ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft · Accepted solution]

Hi @dhannaa ,

Please refer to my steps to see if it helps you.

Create relationships between the three tables.

Then create a measure.

balance = 
IF(SELECTEDVALUE(Table_3[Dimension])=1,"Group4",IF(SELECTEDVALUE(Table_1[Grouping])=2&&SELECTEDVALUE(Table_2[CostCenter])=1,"Group2",IF(SELECTEDVALUE(Table_1[Grouping])=3&&SELECTEDVALUE(Table_2[CostCenter])=2,"Group3","Group1")))

If I have musunderstood your meaning, please provide your pbix without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The best way to accomplish this would be to implement Calculation Groups