UseRelationship

[Dellis81]

Hello

I am needing help with a little accounting magic - flipping between two ledgers dependent on a user selection box. 
In this simple example - income statement w/ledgers going down, and cost centers across.   I would like to "shift" the line indicating "Resource Allocations" to "Service:Bus"

SampleFile:  https://drive.google.com/file/d/1j2l3DK5zj8nd4DNoujcGmoR2L_oT0t72/view?usp=sharing

Within the transactional detail - I have two ledgers Account ID, and Account ID (original).  This measure works perfectly for the standard Account ID.

Amount (std) = 
var sec = min(ReportHeaders[Section])
var secmin = min(ReportHeaders[SectMin])
var secmax = min(ReportHeaders[SectMax])

var group_by = 
    SUMMARIZE(ALLEXCEPT(ReportHeaders, ReportHeaders[Report Name]),[Header], "Amount",

    CALCULATE(sum(TADConsolidated[Amount]), ReportHeaders[Section] <= secmax && ReportHeaders[Section]>= secmin )
    )
var result = sumx(group_by,[Amount]) * 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)

I was hoping I cold tweak the "calculate" function with the userelationship function.

CALCULATE(sum(TADConsolidated[Amount]), ReportHeaders[Section] <= secmax && ReportHeaders[Section]>= secmin,USERELATIONSHIP(TADConsolidated[Account ID Orig],'IncomeBalance x Ledger'[Account]) )

 However, the second result was equivalent to the initial.

When I dump the data to an excel pivot table, and summarize between Account and Account (orginal) I get the results I want by ledger/center.

My goal is a user toggle to flip the Income statement between Accounts IDRESV and 52170



This forum has always been great for solutions.  Thank you!

[MFelix]

Hi @Dellis81 ,

The file is not available is asking for a password.

[Dellis81]

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

[MFelix]

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.

[Dellis81]

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!

[MFelix · Accepted solution]

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.

[Dellis81]

THANK YOU!!!  Thank you again 🙂   Your revised relationship and new measures work great.  AWESOME.   I'm still working thru incorporating into my larger model - so far - exactly what I am needing.

I am finding some of my drill thru's and related interactive reports don't quite work - but suspect I need to go back and rethink how I have those measures setup.

But wanted to give you a big thank you before the day slipped away on me!!