Account scheme reports in Power BI with extra filtering

[Lucian]

Hello,

I need some help with a Profit & Loss report.

With @ImkeF 's help in her article Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX I have managed to use that "magic measure" to display the info using the "ReportsAccountsLayout".

MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation)

 Now, the management came with another ideea - to restrict some calculations based on a new dimension, so the "fact" table has a new tag column and also the ReportsAccountsLayout has this tag column.

For example for a specific "account key" I have to get data only if there is also that "extra dimension" present.

For example there are 2 reports almost the same, except that on report A the account 1 & 2 should be "extra filtered" by the "ExtDim" column.

The ReportsAccountsLayout is in my case named AcctSched

SchedName	RowNo	Desc	Account	ExtDim
A	1	S1	1	Z
A	2	S2	2	X
A	3	S3	3
C	1	S1	1
C	2	S2	2
C	3	S3	3

The fact table look like this

Entry_No	Account	Desc	ExtraDIM	Amount
1	1	A	Z	10
2	1	B		20
3	2	C		30
4	2	D	X	40
5	3	E		50

So when I select report A the report should look like this (with total 100)

But when select report C the total should be 150, and in my case is same as A (100).

To make it work for report "A", I have created in AcctSched a new column combining [Account]&[ExtDim]

= Table.AddColumn(#"Added Custom", "SchedAccDim_PK", each [Account]&[ExtDim])

 Then duplicate this table letting only this "key" to obtain the "interim" table to be able to connect back to Fact table.

The problem is that when report C is selected, because there are "no requirements" to filter for that extra-dimension I should get a total amount of 150 not 100.

What am I missing or "excessive filtering"?

I cannot upload PBIX file directly but I've loaded it here: Filter Extra Dim - Community.pbix 

Kind Regards,

Lucian

[Lucian · Accepted solution]

Well,

After some digging I (think) I found the answer.

Trying to disable the relationship between AcctSched and AcctSched-ExtraDim-Interim and activate it with USERELATIONSHIP only when AcctSched[ExtDim] has a value I found that relationship will never activate.

So digging more to find out why, I found this article: USERELATIONSHIP does not override active relationship because I have also created an "ambigous path".

So, instead of the simple "magic measure"

ActualAmount = CALCULATE([Total Amount],AcctSched)

I have to calculate differently when there is no "extra" AcctSched[ExtDim]:

ActualAmount2 = 
VAR __ExtraFilterValue =
    IF ( HASONEVALUE ( AcctSched[ExtDim] ), MAX ( AcctSched[ExtDim] ), BLANK () )
VAR __Mode1 =
    CALCULATE ( [Total Amount], AcctSched )
VAR __Mode2 =
    CALCULATE (
        [Total Amount],
        ALL ( AcctSched ),
        'Fact'[Account] IN VALUES ( AcctSched[Account] )
    )
RETURN
    IF (
        HASONEVALUE ( AcctSched[ExtDim] ) && ISBLANK ( __ExtraFilterValue ),
        __Mode2,
        __Mode1
    )

And this way it seems to work correctly on the test report.

I did't test it yet on the real report, so if someone have other ideeas or improvements I'm opened to any suggestion.

And the final/working report just if someone will need it (eventually me over some time 😉) :
Filter Extra Dim - Community-Solved.pbix  

Kind Regards,

Lucian