SWITCH TRUE doesn't return when table relationship

[jasonh2hdigital]

I have a basic Power BI report with three tables.

ReportFormat

(1 to many)

ReportChart

(1 to many)

GL

ReportFormat provides a structure I want for a financial report.

ReportChart provids link between ReportFormat and GL.

GL is the transaction table with Date, Chart and Amount.

The report will look something like this (generally Row Index is not seen and ReportItemsNormalised not included).

To bring data into against the ReportFormat using the 'Period Actuals' measure as shown below.

I use this method in a financial report.  It allows me to add in other calcuations into the report - e.g. GP % via calling other measures.

This works really well until I create a relationship between the ReportFormat and ReportChart tables.

When i add the relationship, the 'Period Actual' stops returning other measure called in the SWITCH(TRUE(), other than the 'else' part.  In the below case - it stops returning the Count of Sales.

If I delete the relationship - the Count of Sales works.  I want the relationship because I want to do a drilldown into the GL table details.

I have tried heaps of different measure and calculations in Period Actuals, but all work without the relationship and stop returning when relationship is established.

Happy to share my test pbix file (it is only 57kb).

[v-eqin-msft · Accepted solution]

Hi @jasonh2hdigital ,

Please take a look at the following output based on the relative fields from the three tables:

You could rewrite the original measure Filter(ALL('GL')  like this:

Measure = 
COUNTAX(FILTER(ALL('GL'),RELATED('ReportChart'[ReportItemsNormalised]) IN {"Total Income"}),[Amount])

Then the result works fine:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasonh2hdigital]

Thanks @v-eqin-msft ,

I suspected it had something to do with a filter.  Nice work.  

If I wanted to filter the main report on date, what would I need to reflect this is the measure?

I played with ALLEXCEPT but not getting any sucess.  Any suggestions appreciated.

[jasonh2hdigital]

Thanks @v-eqin-msft  - I can understand very difficult to understand.

I have uploaded a copy of the pbix file in google drive - link below.

https://drive.google.com/file/d/1VMiLhmnVUhUE4_ACMHKgRuqRsjKkM-W9/view?usp=sharing

Hopefully it will help anyone willing to take a look at it.  Good luck!!

[v-eqin-msft]

Hi @jasonh2hdigital ,

Sorry for that the information you have provided is not making the problem clear to me.

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.