topic Re: Measure that filters across two dimensions in DAX Commands and Tips

Measure that filters across two dimensions

Anonymous — Wed, 18 Mar 2020 10:32:54 GMT

I have these facts and dimensions:

factOrders

dimAddress1

dimAddress2

These are the relationships:

dimAddress1 -> factOrders

dimAddress2 -> factOrders

I am trying to create a measure that does this:

Number of Orders with Overseas Addresses := DISTINCTCOUNT of factOrders.OrderID WHERE (dimAddress1.IsOverseas = "Y" OR dimAddress2.IsOverseas = "Y")

Here is my attempt at the DAX:

CALCULATE( DISTINCTCOUNT('factOrders'[OrderID]), 'dimAddress1'[Is Overseas] = "Y" || 'dimAddress2'[Is Overseas] = "Y" )

but it gives the error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Any suggestions?

Re: Measure that filters across two dimensions

Anonymous — Wed, 18 Mar 2020 11:47:41 GMT

This seems to be working:

COUNTROWS( FILTER( SUMMARIZE( 'factOrders', 'factOrders'[OrderID], "IsOverseasOrder", MAX ( MAXX('dimAddress1', IF('dimAddress1'[Is Overseas] = "Y", 1, 0)), MAXX('dimAddress2', IF('dimAddress2'[Is Overseas] = "Y", 1, 0)) ) ), [IsOverseasOrder] = 1 ) )

But it seems far too complicated. Is there a simpler way to do this?

Re: Measure that filters across two dimensions

Greg_Deckler — Wed, 18 Mar 2020 12:39:16 GMT

Perhaps:

COUNTROWS( FILTER( SUMMARIZE( 'factOrders', 'factOrders'[OrderID], "IsOverseasOrder", IF( 'dimAddress1'[Is Overseas] = "Y" || 'dimAddress2'[Is Overseas] = "Y", 1, 0 ) ) ), [IsOverseasOrder] = 1 ) )

Re: Measure that filters across two dimensions

Anonymous — Wed, 18 Mar 2020 14:26:18 GMT

// This formula uses this math formula for sets: // |A + B| = |A| + |B| - |A * B| // Since the formula does not use OR, it // might be faster... but can't guarantee it. # Overeas Orders := var __ordersWithOverseasAddr1 = calculate( distinctcount( factOrders[OrderId] ), keepfilters( dimAddress1.IsOverseas = "Y" ) ) var __ordersWithAverseasAddr2 = calculate( distinctcount( factOrders[OrderId] ), keepfilters( dimAddress2.IsOverseas = "Y" ) ) var __ordersWithBothAddrOverseas = calculate( distinctcount( factOrders[OrderId] ), keepfilters( dimAddress2.IsOverseas = "Y" ), keepfilters( dimAddress1.IsOverseas = "Y" ) ) return __ordersWithOverseasAddr1 + __ordersWithOverseasAddr2 - __ordersWithBothAddrOverseas

It might not be the simplest but it might be fast and it's easily understandable (if you know math, that is).

Best