Filter context not working as expected

Question

Hi all, I have a table with customers IDs, and their first, second and third car purchase dates. With the below formula, what I am trying to do is at any given date, calculate the number of customers, who has ONLY two cars. It is basically count of customers who have purchased their first car, and not yet their second car (So the date should be greater than their first car purchase date and less than their second car purchase date.) This all works fine without any relationship. However when I create a relationship with another table based on customer ID, then somehow the filter context applies, and my numbers are messed up (It has to do something with the dates). Would anyone know how can I rectify this formula to accommodate relationships? It feels like I should put an allexcept somewhere in the formula but I could not figure it out&nbsp;Two_cars_customers_count =VAR&nbsp;tmpEff&nbsp;=&nbsp;ADDCOLUMNS('Device PURCHASE data',"Effective Date",IF(ISBLANK([THIRD_PURCHASE_DATE]),TODAY(),[THIRD_PURCHASE_DATE]))VAR&nbsp;tmpTable&nbsp;= &nbsp;SELECTCOLUMNS(&nbsp; &nbsp;&nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;GENERATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;tmpEff,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Date_Lookup'&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;[Date]&nbsp;&gt;=&nbsp;[SECOND_PURCHASE_DATE]&nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;[Date]&nbsp;&lt;&nbsp;[Effective Date]&nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;[SECOND_PURCHASE_DATE]&lt;&gt;&nbsp;BLANK()&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;"ID",[Customer_ID],&nbsp; &nbsp;&nbsp;"Date",[Date])VAR&nbsp;tmpTable1&nbsp;=&nbsp;GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))RETURN&nbsp;&nbsp;IF(ISBLANK(COUNTROWS(tmpTable1)),0,&nbsp;COUNTROWS(tmpTable1) )&nbsp;Screenshot of my graph without any relationship (Working as expected)&nbsp;&nbsp;I am putting the below relationship&nbsp;&nbsp;Now my graph looks like this (Not expected), most likely due to context filter. (I did not change anything else)&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
Please have a try.
Two_cars_customers_count =
VAR tmpEff =
    ADDCOLUMNS (
        'Device PURCHASE data',
        "Effective Date", IF ( ISBLANK ( [THIRD_PURCHASE_DATE] ), TODAY (), [THIRD_PURCHASE_DATE] )
    )
VAR tmpTable =
    SELECTCOLUMNS (
        FILTER (
            GENERATE ( tmpEff, 'Date_Lookup' ),
            [Date] &gt;= [SECOND_PURCHASE_DATE]
                &amp;&amp; [Date] &lt; [Effective Date]
                &amp;&amp; [SECOND_PURCHASE_DATE] &lt;&gt; BLANK ()
        ),
        "ID", [Customer_ID],
        "Date", [Date]
    )
VAR tmpTable1 =
    GROUPBY (
        ALLEXCEPT ( tmpTable, tmpTable[ID] ),
        tmpTable[ID],
        "Count", COUNTX ( CURRENTGROUP (), [Date] )
    )
RETURN
    IF ( ISBLANK ( COUNTROWS ( tmpTable1 ) ), 0, COUNTROWS ( tmpTable1 ) )

&nbsp;
How to Get Your Question Answered Quickly&nbsp;
&nbsp;
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
&nbsp;
Best RegardsCommunity Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Related content

dbt job schema not working as expected

Power BI: Model is not working as expected

Semantic Model not working as expected

Expression.SyntaxError: Token ',' expected.

Measure Killer not working as expected. What I'm doing wrong?