DAX - Filter a FACT table based on an attribute (field) from another FACT table

[iluvcoding_91]

Hello, so I have two FACT tables and three DIM tables. FACT table one (orders) has a monthly level of granularity, while FACT table two (sales) has a daily level of granularity. Both FACT tables are related via the three DIM tables up top. I'm trying to find a way to sum the orders in the Orders FACT table based on an attribute that exists in the Sales FACT table (this attribute does not exist in any of the DIM tables, it only exists in the Sales FACT table). Is there a way I can use DAX to calculate the total orders by customer based on this attribute that only exists in the sales table? For example, sum the orders for customers where the Sales attribute equals "true"?

[Anonymous]

Hi!

With the information provided I think you could create a calculated column in the customer dimention table. If this is the model:

And this is the Fact Sales:

As you can see I created different line of Sales for the customer 03, assuming that the flag can change for the same customer.

To give True if at least one of the Sales rows for the customer is true, create the calculated column:

Sales Flag = CALCULATE(MAX(Sales[Flag]), RELATEDTABLE(Sales))

Otherwise, if you want to give false if at least one row is false:

Sales Flag = CALCULATE(MIN(Sales[Flag]), RELATEDTABLE(Sales))

Once you have this flag just calculated the measure like:

Measure for customer True = CALCULATE([Measure], Customer[Sales Flag] = 1)

I hope I answered your question,

Have a good day,

Marisol