Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
dhannaa
Helper IV
Helper IV

Dynamic conditional usage of dimension tables

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. 

dhannaa_0-1643189242839.png



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

dhannaa_1-1643188633254.png

 

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

dhannaa_2-1643188662935.png

 

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 IDGroup 
1Group1Filtered only by Accounts
2Group2Filtered by Accounts And Cost Centers
3Group3Filtered by Accounts And Cost Centers
4Group4Filtered by Accounts and Dimensions

 

Grouping Account 
  
AccountGrouping
50001
50011
50021
50031
50041
50052
50062
50072
50053
50063
50073
50104
50114
50124

 

Grouping Cost Centers 
  
CostCenterGrouping
12
23

 

Grouping DimX 
  
DimensionGrouping
14

 

Accounting Data    
     
AccountCostCenterDimXAmount 
50001150,00 €Group1
500122100,00 €Group1
500232200,00 €Group1
500342300,00 €Group1
50045150,00 €Group1
500512100,00 €Group2
500613200,00 €Group2
500714300,00 €Group2
500525300,00 €Group3
50062650,00 €Group3
500723100,00 €Group3
5010121200,00 €Group4
5011131300,00 €Group4
501214150,00 €Group4





1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @dhannaa ,

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

Create relationships between the three tables.

vpollymsft_0-1643697717670.png

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")))

vpollymsft_1-1643697773541.png

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.

 

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi @dhannaa ,

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

Create relationships between the three tables.

vpollymsft_0-1643697717670.png

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")))

vpollymsft_1-1643697773541.png

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
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.