having measure reference a separate "flag measure"

[GunnerJ]

Below is a code sample that is nearly completed. The only issue is that is the "result" variable I want to check if the meaure 'New Gig Customers' has been flagged. When I add it to the calculate function I get the message "A function 'Placeholder' has been used in a T/F expression that is used as a table filter". I know it's likely simple but what would be the best way to reference the value in that measure?

Still Upgraded? = 

        var last_date =
           CALCULATE(
                LASTNONBLANK(Charges[CHG_DATE],1),
                  ALLEXCEPT(Charges, Charges[Charge Cust#]), 
                    Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[StartDate]) &&
                        Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[End Date + 90]) &&
                            Charges[ACTION_FLAG] = "START"
                 )

        --var prev_up = IF([New Gig Customers] = 1, 1,0)

        var result =
            CALCULATE(LASTNONBLANK(Charges[CHG_DESC],1),
                ALLEXCEPT(Charges,Charges[Charge Cust#]),
                    Charges[CHG_DATE] = last_date,
                        Charges[CHG_DESC] IN {"Basic 100", "Basic 100 OK"},
                            Charges[Prev Int Flag] IN {"Gigabit 1000", "Gigabit 1000 OK"})
                                
            

        return result

Below is the measure in question.

New Gig Customers = 
    CALCULATE( DISTINCTCOUNT(Charges[Charge Cust#]),
        ALLEXCEPT(Charges,Charges[Charge Cust#]),
            Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[StartDate]),
                Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[EndDate]),
                    Charges[CHG_DESC] IN {"Gigabit 1000", "Gigabit 1000 OK"},
                        Campaign[Campaign_Reason__c] = "Cross sell – Upgrade Internet",
                            Charges[ACTION_FLAG] = "START"
    )

 Any help is appreciated!

[vanessafvg]

see if this works

New Gig Customers =
VAR startdte =
SELECTEDVALUE ( Campaign[StartDate] )
VAR enddate =
SELECTEDVALUE ( Campaign[EndDate] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Charges[Charge Cust#] ),
ALLEXCEPT ( Charges, Charges[Charge Cust#] ),
FILTER (
Charges,
Charges[CHG_DATE] >= startdte
&& Charges[CHG_DATE] <= enddate
&& Charges[CHG_DESC]
IN { "Gigabit 1000", "Gigabit 1000 OK" }
&& Charges[ACTION_FLAG] = "START"
),
FILTER (
Campaign,
Campaign[Campaign_Reason__c] = "Cross sell – Upgrade Internet"
)
)

[GunnerJ]

@vanessafvg I'm sorry I think I confused you. "New Gig Customers" works as intended, however I'm not able to reference it in the measure "Still Upgraded?". Is there a way to check the value of "New Gig Customers" within "Still Upgraded?".

Thank you for the reponse and sorry for the confusion. 

[vanessafvg]

😂

where are you trying to use it though.  You have declared the variable and commented it out, but i can't see where you using it in your Still Upgraded.

[GunnerJ]

@vanessafvg  Within the Result variable. I've added what I'd originally attempted at the end of the snipit but it gives me the "Placeholder" error message. The ideal is to only look at the customers that were originally marked in the 1st query and to see if they still are upgraded. I'm just not sure how to reference that previous measure. 

    var result =
            CALCULATE( DISTINCTCOUNT(Charges[Charge Cust#]),
                ALLEXCEPT(Charges,Charges[Charge Cust#]),
                    Charges[CHG_DATE] = last_date,
                        Charges[CHG_DESC] IN {"Basic 100", "Basic 100 OK"},
                            Charges[Prev Int Flag] IN {"Gigabit 1000", "Gigabit 1000 OK"},
                                [New Gig Customers] = 1)

[vanessafvg]

its a bit tricky to test without data, but i wonder if just adding in a filter to the statement will fix it

VAR result =
    CALCULATE (
        LASTNONBLANK ( Charges[CHG_DESC], 1 ),
        ALLEXCEPT ( Charges, Charges[Charge Cust#] ),
        FILTER (
            charges,
            Charges[CHG_DATE] = last_date
                && Charges[CHG_DESC]
                IN { "Basic 100", "Basic 100 OK" }
                && Charges[Prev Int Flag]
                IN { "Gigabit 1000", "Gigabit 1000 OK" }
                && [New Gig Customers] = 1
        )
    )
RETURN
    result

[GunnerJ]

@vanessafvg 
thank you! the filter function got rid of my error. If you wouldn't mind taking a look at the sample file and seeing page "Upgrade Test". The new measure works but doesn't sum at the campaign level. 

Here's a snippit. The measure "New Gig Customers sums at the campaign level but if I take out the "Charge Cust#" field the "Still Upgraded?" values disappear. If that's able to be resolved I'd have my final needed solution. 

Thank you again for your quick responses!

Link to file

https://www.dropbox.com/s/3szh0jartf11ske/Marketing%20Campaigns%20Dashboard.pbix?dl=0

[vanessafvg]

if you dont have the row context you will probably need to iterate through it by using a sumx

 VAR result =

            CALCULATE (

            SUMX( VALUES(Charges[Charge Cust#]),

                CALCULATE(DISTINCTCOUNT(Charges[Charge Cust#]),

                   ALLEXCEPT ( Charges, Charges[Charge Cust#] ),

                      FILTER (

                            charges,

                                Charges[CHG_DATE] = last_date

                                    && Charges[CHG_DESC]

                                        IN { "Basic 100", "Basic 100 OK" }

                                            && Charges[Prev Int Flag]

                                                IN { "Gigabit 1000", "Gigabit 1000 OK" }

                                                    && [New Gig Customers] = 1

                            )

            )))

[GunnerJ]

Sumx unfortantly is giving me the same results. I tried commenting out the AllExcept but didn't have luck there either 

[vanessafvg]

ok maybe I am not understanding what the business rule here is.   Can you please explain to me what your expected result is and giving an example if possible , or can i assume the 1 record with the still_updated= 1 is correct.   What are you measuring from a business perspective.

[GunnerJ]

@vanessafvg 

The ultimate goal is to check and see if customers are stil upgrade 90 days after the enddate of a campaign. In "New Gig Customers" measure it checks for customers that upgraded during the campaign. This new measure looks at the last "START" date that's less than 90 days out to see if they're still upgraded. 

Currently the new measure "Still Upgraded?" flags customers that downgraded so it's technically the inverse of the original measure but I just want to subtract it from the "New Gig Customers" total so it's fine. 

The example below is correct. It's flagging a customer that is no longer upgraded. 

The hope is that I can sum those customers up into a grand total, subtract from the original amount, and then show the % that stayed upgraded.

I believe all of the measures work but "Still Upgraded?" seems to depend on seeing the customer# in its filter context instead of summing like the oringinal measure. 

Does this help?

[vanessafvg]

ok i think this should resolve it now.  your lastdate variable wasn't working when you remove the customer, it brought back the incorrect date, because if you dont provide the customer context on the row you do need to provide it elsewhere, i failed to add the variable in.

Still Upgraded? =
VAR cust =
SELECTEDVALUE ( Charges[Charge Cust#] )
VAR last_date =
CALCULATE (
LASTNONBLANK ( Charges[CHG_DATE], 1 ),
ALLEXCEPT ( Charges, Charges[Charge Cust#] ),
Charges[CHG_DATE] >= SELECTEDVALUE ( Campaign[StartDate] )
&& Charges[CHG_DATE] <= SELECTEDVALUE ( Campaign[End Date + 90] )
&& Charges[ACTION_FLAG] = "START"
&& Charges[Charge Cust#] = cust
)
VAR result =
CALCULATE (
DISTINCTCOUNT ( Charges[Charge Cust#] ),
ALLEXCEPT ( Charges, Charges[Charge Cust#] ),
FILTER (
charges,
Charges[CHG_DATE] = last_date
&& Charges[CHG_DESC]
IN { "Basic 100", "Basic 100 OK" }
&& Charges[Prev Int Flag]
IN { "Gigabit 1000", "Gigabit 1000 OK" }
&& [New Gig Customers] = 1
&& Charges[Charge Cust#] = cust
)
)
RETURN
result

[GunnerJ]

@vanessafvg 
I see where the customer # column is not needed but the measure unfortunantly still requires an customer number to be filtered on. In the visual that tries to focus on the campaign only both the original and the new (renamed "Test Upgrade") lose context. What confuses me is how "New Gig Customers" works but the others need very specific context. 

I do appreciate your efforts in helping me. 

[vanessafvg]

new gig customer is counting the unique customer number, so it still uses the customer reference, even if to aggregate.