Forum Discussion

Dellis81's avatar
Dellis81
Post Prodigy
5 years ago
Solved

UseRelationship

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!

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

6 Replies

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        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.