Table shows irrelevant values from "many" side of one-to-many relation

[michaelsh]

I have Invoices table that is connected to SalesOrders tables with many-to-one relation.

(There could be multiple Invoices connected to one Sales Order, there also can be Sales Orders without any invoice)

The simple star-schema is below.

Also, here is the link to .pbix:

https://1drv.ms/u/s!AoP_9ampPIT7gZUVjqRfj-CcNU7I_w?e=6fK5hG

I want to show a simple table (or matrix without sub-totals) showing the following:

Sales Order, SO Amt, Invoice Number, Invoice Amt

If I do a simple SO Amt = SUM('Sales Orders'[SO Amount]) then for each SO it shows all the unrelated invoices (see the left side of powerbi screenshot)

I tried to do a measure: SO Amt (isfiltered) =

IF(ISFILTERED(Invoices),BLANK(),SUM('Sales Orders'[SO Amount]))

But then, only subtotals are shown correctly.

I do want the SO Count and SO Amt to repeat but only for related invoices and I don't want to show subtotals.

Below is the layout I want.

Please help

Thanks

[michaelsh · Accepted solution]

Ok, I think I've solved this (after two beers...)

If any one finds a more elegant solution - please let me know...

Here is the solution file: 

https://1drv.ms/u/s!AoP_9ampPIT7gZUavYdk3iSFw8A7Vw?e=OMCJJd

I had to add a "dummy" invoice "No Invoices", so it shows instead of blanks

the measure is:

SO Amt solved =

VAR SOInvInd = IF(ISBLANK(CALCULATE( MAX(Invoices[Invoice Number]),all(Invoices[Invoice Number]))),FALSE(),TRUE()) //true if SO has an Invoice

VAR NoInv = IF(CALCULATE(SELECTEDVALUE(Invoices[Invoice Number]),ALL('Sales Orders'))="No Invoices",TRUE(),false()) //true if current line is No Invoice

return

SWITCH(TRUE(),

NOT(ISFILTERED(Invoices)),[SO Amt],

SOInvInd,CALCULATE([SO Amt],Invoices),

NoInv,[SO Amt],

BLANK()

)