Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kuji
Frequent Visitor

How to ignore relationships in calculation when using SELECTEDVALUE

Hi 

 

I am having trouble getting a DAX expression to work and have read tutorials but getting nowhere!

 

I am creating a measure that will calculate a value depending on the row header name. However, the measure does not display the way I would like.

 

I have a star-schema data model which is causing me an issue.

 

Below is an example of my issue.

 

I would like to use the With relationship model but I cannot get the value I want using my measure.

 

My DAX formula is:

 

ShowActual = CALCULATE(SUM('Fact GL'[Amount]),'Dim CoA'[Account number = "100"])

 

In the table below, where my measure shows Select Value = TRUE (this is my condition), it should show the calculation.

 

The logic is that if Select Value is TRUE, give me the SUM of accounts codes 100. I know why it is not displaying - it is because the Header Description is not in the CoA table, so no match and does not show any values. 

 

However, I would like PBI to ignore this and show me the measurement I created.

 

Where there is a match, then follow the relationship mode, but for some headers, I would still like to display the sum myself through a customer calculation with specific filters (i.e. accounts codes = 100)

 

Getting the results by breaking the relationship

In the Without Relationship illustration, the correct value appears, but then I lose my relationship which I need for drilling down. The Switch Value basically says if Select Value is TRUE, show me ShowActual.

 

How can I get the results from the Without Relationship table but maintaing the With Relationship star schema?

 

 

Thanks in advance!

 

Capture.PNG

 

 

 

1 ACCEPTED SOLUTION

Hi ThoSch-Ger,

 

Its OK. I've worked it out.

 

I created the measurement I needed and it shows in the table.

 

I used CALCULATE with FILTER and used the ALL function.

 

Previously, I was not using FILTER in CALCULATE (only its in built one) and was using ALL on the wrong table/column.

 

I used the following DAX expression:

 

CALCULATE(SUM('Fact GL'[Amount]),
    FILTER(

                ALL('Dim CoA',

                          'Dim CoA'[Account Code] = "100")

)

 

It return all the rows from the table and includes Parent 1 and 2.

 

 

View solution in original post

4 REPLIES 4
ThoSch-Ger
Helper I
Helper I

Why do you need the Layout Table? 

You have the header name already in the Dim CoA.

Probably that's why the without method is working and the with method stands in conflict.

 

ShowActual = Calculate( Sum( 'Fact GL[Amount]'; 'Fact GL[Account number]' = 100)


Slice by 'Dim CoA[Header name]' and you should be fine.

 

 

Hi 

 

The reason is because I have to show a table with two parents and one child.

 

For example (this is for illustration purposes only)

 

Header A (Parent 2)     $XXX

Header B (Parent 1)     $XXX

 

Both heading names are pointing to the same account codes. I cannot do this with the method you mentioned as I would only see Header B (Parent 1).

 

This is because Header A (Parent 2) is not mapped in the CoA, but Header B is. If I map Header A to the CoA, then I get multiple parent child relationships and run into problems.

 

I know it does not make sense but that's not the issue (accountants generally don't make sense)

 

The measurement I am trying to create is that if the measurement I created to test the condition SELECTEDVALUE is FALSE, then use the CoA table and calculate as normal (this works fine), however, if you see TRUE (this is not in the CoA table) then perform a custom calculation on the GL table where I will choose the appropriate account code filters. I want PBI to act like the relationship is not there because when I remove that relationship I get the desired result.

 

Maybe its the wrong way to go about it but any suggestions would help.

 

 


@kuji wrote:

 

Both heading names are pointing to the same account codes. I cannot do this with the method you mentioned as I would only see Header B (Parent 1).

 

This is because Header A (Parent 2) is not mapped in the CoA, but Header B is. If I map Header A to the CoA, then I get multiple parent child relationships and run into problems.

 


If this is true you cannot reach Parent 2 through Parent 1.

You have to create 2 outgoing relationships from the Fact Table to both Header Tables. Both Header Tables have to contain the account number.

 

then you can switch the relationship through IF combined with USERELATIONSHIP depending on the account number from the fact table.

 

if account.no = 100; USEREALTIONSHIP( Header A; Fact) else USEREALTIONSHIP( Header B; Fact)

Hi ThoSch-Ger,

 

Its OK. I've worked it out.

 

I created the measurement I needed and it shows in the table.

 

I used CALCULATE with FILTER and used the ALL function.

 

Previously, I was not using FILTER in CALCULATE (only its in built one) and was using ALL on the wrong table/column.

 

I used the following DAX expression:

 

CALCULATE(SUM('Fact GL'[Amount]),
    FILTER(

                ALL('Dim CoA',

                          'Dim CoA'[Account Code] = "100")

)

 

It return all the rows from the table and includes Parent 1 and 2.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.