Forum Discussion

gjensen's avatar
gjensen
Frequent Visitor
3 years ago

Filter referencing Measure being ignored

The measure below always returns no values even though I know based on the criteria shown I should have some records flagged. 

The piece that's preventing it from working properly is the FILTER(Charges, [New TV Upgrade Customers] = 1). It is referencing another measure to see if customers are in the designated group. When I take that section out I return some results but not the actual full set needed. I've provided the upgrade customers measure as well if it helps. 

 

Stopped TV Upgrade Customers = 

    CALCULATE( DISTINCTCOUNT(Charges[Charge Cust#]),
         FILTER(Charges, [New TV Upgrade Customers] = 1),
            ALLEXCEPT(Charges,Charges[Charge Cust#]),
                Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[EndDate]),
                Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[End Date + 120]),
                    Charges[CHG_DESC] IN {"Ultra TV", "Advanced TV", "Complete TV", "Local Plus TV"},
                        Campaign[Campaign_Reason__c] = "Cross sell – Upgrade TV",
                            Charges[ACTION_FLAG] = "START"
)  
New TV Upgrade Customers = 
    CALCULATE( DISTINCTCOUNT(Charges[Charge Cust#]),
        FILTER(Campaign, Campaign[Campaign_Reason__c] = "Cross sell – Upgrade TV"),
        FILTER(Charges, [$ First Upgrade TV Benefit] <> BLANK()),
        ALLEXCEPT(Charges,Charges[Charge Cust#]),
        Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[StartDate]),
        Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[EndDate]),
        Charges[ACTION_FLAG] = "START")

 Below shows how no customers are being flagged out of the "New TV Upgrade Customers" segment. 

Any help in explaining why referencing this measure would cause the full measure to go blank would be greatly appreicated. I'll be happy to provide any additional information needed. 

Need Help
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    gjensen , Measure has to filter by dimension or grouped value

     

    example

    FILTER(customer, [New TV Upgrade Customers] = 1).

     

    Sumx( FILTER(values(Charges[name]) , [New TV Upgrade Customers] = 1) , [Measure] )

     

    you can check the example

    Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

    • gjensen's avatar
      gjensen
      Frequent Visitor
      3 years ago

      amitchandak Thank you for the reply.
      I'm just a bit confused on the direct application to my data.

       

      I can use the sumx measure to show the "New TV Upgrade Customers" but I need to then filter those customers using the logic shown in the Stopped TV Upgrade Customers measure. 

      I tried adding it's logic to my existing measure but said a True/False expression doess not specifcy a column (unsure where it's coming from)


      Is this close? The "return" piece has the correct logic IF I can get it to look at just the values flagged in the New TV Upgrade Customers measure. 

       

      • amitchandak's avatar
        amitchandak
        Super User
        3 years ago

        gjensen , Try like

         

        Stopped TV Upgrade Customers =

        CALCULATE( Countx(filter( Values(Charges[Charge Cust#]), [New TV Upgrade Customers] = 1), [Charge Cust#]),
        ALLEXCEPT(Charges,Charges[Charge Cust#]),
        Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[EndDate]),
        Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[End Date + 120]),
        Charges[CHG_DESC] IN {"Ultra TV", "Advanced TV", "Complete TV", "Local Plus TV"},
        Campaign[Campaign_Reason__c] = "Cross sell – Upgrade TV",
        Charges[ACTION_FLAG] = "START"
        )