Formula works one way but not the other way ????

[masplin]

I have 2 tables Events and Support Package Items. Each event has multiple lines in the other table.

This formula correctly identifies there are 17 rows in SPI table with same associate and start date

part count = CALCULATE(COUNTROWS(Support_Package_Items),
                        FILTER(Support_Package_Items,
                     Events[Associate]=Support_Package_Items[Associate] &&
                     Events[Event Start Date]=Support_Package_Items[Start Date]

                      ))

So I was epxecting the opposite formula in the SPI table would come up with the answer 1 for rows in the event table, but its just blank. The fact the top one works means the comparison part is fine.

Event cost = CALCULATE(COUNTROWS((Events)),
                    FILTER(Events,
                       Events[Associate]=Support_Package_Items[Associate]  &&
                       Events[Event Start Date]=Support_Package_Items[Start Date]

                      ))

I've tried creating ID from assoicate andstart on both tables and relating them, but that doens't work. Really no idea why this doens't work as seems extremly simple request???   I have lots of other relationshipsin the modle but assume they play no role as not in the calculation? 

Sadly big andconfidential modle so before i start trying to cut it down to post any obvious thoguht son why this might be? Much appreciated

Mike

[AlexisOlson]

What is the relationship (if any) between these tables? Also, are these measures or calculated columns? If they are calculated columns, what table is each defined on?

[masplin]

sorry they are both calculated columns. first one is on the Event table and second one is on the SPI table

There are some relationships between these tables and a date table and also between the associateid on both tables and a central associate table to find the [Associate]. 

[masplin]

So my tree is quite complicated

Projects

    |             \ by Projectid

Events         Particpants

    |                       |     by Participantid

    |               Support Package Items

   |              / by associateid

Associate

There is no direct field relating SPI and events. all relationships are single. If I delete the Participant table the formula now works. is it trying to go up the tree via particpant to project and then cant come down the event branch because the relationship is wrong way?  

I just wasnt expecting the relationships to play a role if not in the formula, but maybe they affect the filtering?

[AlexisOlson]

It looks like it's a matter of SPI filtering Events but not vice versa.

In the second expression, when you write FILTER ( Events, ... ), the Events table is already filtered before it even starts checking the conditions. It's the sub-table that corresponds to just the current row in SPI table via your model relationships.

This doesn't happen in the first expression since filtering only propagates "downstream" in the relationship diagram.

[masplin]

So any solution? I tried putting ALL(Events) in the second expression to remove the filtering coming via the rlationships but doesn't work. 

The events table woudl be filtered either by the relationship via project whch woudl be fine as the event is for the same project or via associate which is also fine as counting for the same associate.  So I woudl have thoguht any pre filtering of the events tablestil lincluded the rwos I wanted to count and not be blank???

[masplin]

I turned off the project-event relationship no difference. Also between associate and event

only tunring off the SPI-particpant relationship worked. Is there anopposite of userrleationship to not use it?

[AlexisOlson · Accepted solution]

Try removing the CALCULATE to avoid the context transition turning row context into filters that need to be propagated:

Event cost =
COUNTROWS (
    FILTER (
        Events,
        Events[Associate] = Support_Package_Items[Associate]
            && Events[Event Start Date] = Support_Package_Items[Start Date]
    )
)

[masplin]

Brilliant thnaks so much