cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dellis81
Post Patron
Post Patron

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


IncomeStatement.PNG

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

ExcelExample.PNG

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

1 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:

 

MFelix_0-1621251729379.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Dellis81 ,

 

The file is not available is asking for a password.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

MFelix_0-1621243109343.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

 

MFelix_0-1621251729379.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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!!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!