Forum Discussion
UseRelationship
Hi Dellis81 ,
Sorry for the incorrect model I have send out, I tough I had saved the file but it did not happen.
Has I refered the changes are based on the accounts and transforming the relationships from many to many to one to many:
Has you can see in the image above I have create a new table Accounts (with distinct values) then I made the relationship between this table and the other to having a one to any (also the inactive relationship). There is also a change on the report headers table making it part of a one to many relationship.
All the opther relationships are the same.
Correct file attach.
Try again - including both OneDrive and Google links.
Thank you!
One Drive: https://1drv.ms/u/s!AmBVCme14p7xsjI34Vnkbz3Vak-A?e=OMkZgg
Google: https://drive.google.com/file/d/1czHXHqLS0XGjmif4OBYdkhzKsISGKaZH/view?usp=sharing
Hi Dellis81 ,
the use of the many to many relationships with the bidirectionality in all of these makes the result to be very tricky.
Believe that you need to change the way your model is setup. I have made some changes on the relationships creating a single table for the accounts and making one to many relationships withs TAD and income and also created the following measures:
Account ID V2 = VAR sec = MIN ( ReportHeaders[Section] ) VAR secmin = MIN ( ReportHeaders[SectMin] ) VAR secmax = MIN ( ReportHeaders[SectMax] ) VAR group_by = SUMX ( CALCULATETABLE ( SUMMARIZE ( 'IncomeBalance x Ledger', ReportHeaders[Report Name], ReportHeaders[Section], ReportHeaders[SectMin], ReportHeaders[SectMax], ReportHeaders[LineNumber], Accounts[Account], "@AmountTotal", SUM ( TADConsolidated[Amount] ) ), ALLEXCEPT ( ReportHeaders, ReportHeaders[Report Name] ), ReportHeaders[Section] <= secmax && ReportHeaders[Section] >= secmin ), [@AmountTotal] ) VAR result = group_by * MIN ( ReportHeaders[NormBal] ) VAR result_wo_NI = IF ( ISFILTERED ( 'IncomeBalance x Ledger'[Account] ) || ISFILTERED ( 'IncomeBalance x Ledger'[Index] ), IF ( sec == 0, BLANK (), result ), IF ( ISBLANK ( result ), 0, result ) ) RETURN IF ( result_wo_NI = 0, BLANK (), result_wo_NI )Account ID V2 USERELATIONSHIP = CALCULATE([Account ID V2], USERELATIONSHIP(Accounts[Account], TADConsolidated[Account ID Orig]))Believe the result is what you need however since you have so little details this can have issues with other parts of your model.
Thank you Sir!
The screenshot provided appears to be the expected result. GREAT!! However, I am unclear as to your changes in relationships and accounts. The attached file, I believe was the original file I submitted.
I apologize, I tried to keep things simple in my example. I sensed providing the full magnitude would be overwhelming. The struggle in my bigger model - was subtotals - that forced me to go with bi-directional relationships. Looking forward to understanding your changes in the model structure!!
Thank you again!Hi Dellis81 ,
Sorry for the incorrect model I have send out, I tough I had saved the file but it did not happen.
Has I refered the changes are based on the accounts and transforming the relationships from many to many to one to many:
Has you can see in the image above I have create a new table Accounts (with distinct values) then I made the relationship between this table and the other to having a one to any (also the inactive relationship). There is also a change on the report headers table making it part of a one to many relationship.
All the opther relationships are the same.
Correct file attach.