Calculate Amount from Another Table with Many to Many Cardinality and Hide Blank Rows

Power BI and DAX gurus,

Below is the print screen of the tables and simplest relationship that I had.

The top left of the print screen is the raw result that I further want to modify

1. Hide all the blank rows (tried using AND(INSCOPE([...]), ISBLANK(VALUES([...]))
2. Want to have the amount populated from factTable based on the Row Context (BUT ONLY AT CERTAIN ROWS).

Notice here that, those rows where the values are bold, some are subtotals where PowerBI need to automatically SUM them, and some need to have amount populated from factTable.

So looking at the print screen, using Cat 1 as an example:

• row 1 - 9400. This is a subtotal where PowerBI should automatically SUM Cat1, Cat2 and Cat3.
• row 2 - Cat 1. This is a subtotal where PowerBI should automatically SUM from row 3 (7130) and row 10 (9400).
• row 3 - 7130. This is a subtotal where PowerBI should automatically SUM from row 4 (5101) and row 8 (7130).
• row 4 - 5101. This is a subtotal where PowerBI should automatically SUM from row 5 (1000), row 6 (3333) and row 7 (5101).
• row 5 to 8 and row 10. These are NOT a subtotal and needs to get the SUM from factTable based on row and filter context (i.e. FY_Period, id and costcode).
• row 9 - Blank. Hide / Do not show in table matrix

The issue that I'm facing right now here is, in my factTable, my [id] have many to many relationships AND it's looking at 3 different columns in the dimTable (i.e. factTable[id] *<-->* dimTable[Dest1->To]  ,  factTable[id] *<-->* dimTable[Dest2->Dest1]  ,  factTable[id] *<-->* dimTable[From->Dest2] ) note here that the solid line in the print screen shows the active relationship

What I'm trying to do here is to show the amount that flows from 1 "destination" to another "destination", that's why you may be seeing duplicates id (i.e. 9400 at row 1 and row 10, 7130 at row 3 and row 8, 5101 and row 4 and row 7 etc). The simplest way to know what "id" takes from factTable needs to look at the dimTable.

The following is the logic that needs to take value from factTable, otherwise it's a subtotal that needs power bi to automatically sum like pivot table:

• All "id"s that are in dimTable[From->Dest2] (shown in row 5, 6 and 7)
• "id"s that are in dimTable[Dest2->Dest1]  BUT with dimTable[From->Dest2] = null (shown in row 😎
• "id"s that are in dimTable[Dest1->To]  BUT with dimTable[Dest2->Dest1] = null (shown in row 10)

https://www.dropbox.com/scl/fi/hwn3qvj76up18lxp4c1gg/Calculate-Amount-from-Another-Table-with-Many-to-Many-Cardinality-and-Hide-Blank-Rows.pbix?rlkey=u2m9j1zgmtl8zihc1cwk0h6w2&st=7dgaorg7&dl=0 

https://drive.google.com/file/d/1yjeiDlgKRF7gbcxUznYPWANGwzf4v7Jd/view?usp=drive_link