Creating a flag from one fact table to filter another fact table

[arndtvissing]

Hi all

I have a problem I am trying to solve. I am trying to see if I can solve this using just DAX instead of having to change the data model.

I have 2 fact tables, one that contain sales volume which is by order number, and a second fact table that contains the order number, the account number and the revenue.

These are then linked to a order table, and the revenue fact table is linked to an account table. A classic star schema model.

Now, what I want to achieve is to make a dynamic filtering on my volume table, depending on the account that has been selected in my account dimension. If an order has revenue on the specific account, then the visual should show the corrosponding sales order. If the order number does not have the selected account, then it should not show the sales volume.

Any good tips on how to do this? I tried using variables and calculatetable but I am getting stuck. Hoping for some help.

Volume fact table

Order Number	Volume
s	10
s2	5

revenue Fact table

order number	account	revenue
s	a1	5
s	a2	4
s	a3	10
s	a4	8
s2	a1	92
s2	a4	5

and then 2 dimension tables, below "Order number" with 1-* relationships to both fact tables

Order number

s

s2

and account dimension with 1-* tot he revenue fact table only. This is the table i want to use for my parameter selection

Account

a1

a2

a3

a4

[PaulDBrown]

@arndtvissing 
any chance you can show a depiction of the expected outcome?

I'm not sure what exactly needs to be shown. 

See image