Many to Many and Row Context

[PaulGotts]

I fear I may be travelling deeper and deeper into this rabbit hole called DAX and would like to know if I am at all on the right track for what I am trying to do...

Some background (as simple as I can explain):

I am attempting to obtain a count of stores that have the following attributes:​

i) they each use the same version of Type "C000"
ii) they meet certain sales criteria.
​

My data model is arranged with several tables/columns, the relevant ones include 'Type', 'Stores', and 'SalesData'...

'Type' - Store number, Type
'Stores' - Unique list of Store Numbers
'SalesData' - Store number, Product number, Product style A or B, Unit Sales, Dollar Sales, Average Store Unit sales of all of Products A minus Average Store Unit Sales of all of Products B, Dollar sales of Product A minus Dollar sales of Product B​

I have made the following formula:

=CALCULATE(COUNTROWS(Type),

FILTER(ALL(Type),​

COUNTROWS(​

FILTER(Type,​

MID(EARLIER(Type[Type]),17,4)="C000"&&​

EARLIER(Type[Type])=Type[Type]&&​

RANK.EQ(EARLIER(Type[Type]),Type[Type],1)<3)​

)>0​

),​

FILTER(Stores,​

CALCULATE(​

COUNTROWS(SalesData),​

FILTER(SalesData,
SalesData[UnitSalesPAminusPB]>0||
(2*ABS(SalesData[UnitSalesPAminusPB]))<salesdata[dollarsalespaminuspb])< div="">)>0
)
)


</salesdata[dollarsalespaminuspb])<>​

The first Filter is attempting to get the Earlier function to work outside of a Row Context.
<salesdata[dollarsalespaminuspb])< div="">The second Filter is attempting to get the Type table sorted by SalesData (via 'Stores') - ie. Many to Many relationship.
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">Trying to get both filters to be applied to the Type table and get a count of the remaining rows (stores).
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">Any thoughts regarding this? Wondering if I need to put up the 'Boot Call' to be saved by the Italians.
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">Thanks kindly,
</salesdata[dollarsalespaminuspb])<>
<salesdata[dollarsalespaminuspb])< div="">Paul.</salesdata[dollarsalespaminuspb])<>

 

[PaulGotts]

Nevermind everyone I was able to come up with a solution to this problem... attached below with some basic explanation for those interested (or those whom know of easier solutions).

Solution:=
CALCULATE(COUNTROWS(Type),

FILTER(ALL(Stores),​

CALCULATE(COUNTROWS(​

FILTER(Sales,​

Sales[unitsPAminusPB]>0||(2*ABS(Sales[unitsPAminusPB]))​

<sales[dollarspaminuspb])< div="">)
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
)>0
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
),
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
FILTER(ALL(Type),</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
C</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
OUNTROWS(</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
FILTER(Type,</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
MID(EARLIER(Type[Type]),17,4)="1234"&&
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
EARLIER(Type[Type])=Type[Type]&&</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))

RANK.EQ(EARLIER(Type[Type]),Type[Type],1)<3
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
)
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
)
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
)
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>​

<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))
</sales[dollarspaminuspb]))
<sales[dollarspaminuspb]))
)
</sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>
<sales[dollarspaminuspb])< div=""><sales[dollarspaminuspb]))


The first Filter obtains only those rows which meet the store level selling criteria.
The second Filter obtains only those rows which meet the store level Type criteria.

Applying both these filters to the Power Pivot table obtains a count of those stores which not only share the Type[Type] but also surpass the specific sales metrics.

There are a few more intricacies here but that is the basic idea.

Thanks again team!
Paul. </sales[dollarspaminuspb]))
</sales[dollarspaminuspb])<>