SUM column based on two different filters for same unique ID

[Anonymous]

We record our promo codes for membership and our actual membership purchases as seperate line items in table call 'Activity'. There is no relationship between the two line items, except they're both recorded under the same member ID. Additionally, their member type is based on their latest 'Product_Code' purchased with a valid 'THRU_DATE'. I have some code that creates a measure to pull the MAX 'SEQN' for 'ACTIVITY_TYPE' DUES and DUESOFFER to ensure we're grabbing the most recent dues and dues offer transaction for a member. Sample data below:

ID	LAST_NAME	FIRST_NAME	PRODUCT_CODE	ACTIVITY_TYPE	THRU_DATE	TRANSACTION_DATE	UF_1	AMOUNT	SEQN	LatestDUESOFFER_SEQN	LatestDUES_SEQN
9E+08	Nonya	Business	ABC	DUESOFFER	9/30/2021 0:00	9/20/2020 0:00	Vgabf2020	$0	1232405	1232405
9E+08	Nonya	Business	ABC-1YR	DUES	9/30/2021 0:00	9/20/2020 0:00	Approved	$49	1232404		1232404
9E+08	Nonya	Business	ABC-1YR	DUES	8/31/2020 0:00	8/1/2019 0:00	Approved	$43	1123159
9E+08	Nonya	Business	ABC	DUESOFFER	7/31/2019 0:00	7/6/2018 0:00	WOODBOOK	$0	969559
9E+08	Nonya	Business	ABC-1YR	DUES	7/31/2019 0:00	7/6/2018 0:00	Approved	$43	969557

My goal is to figure out total revenue for a given promotion code that we run. So expected results from sample data above is  DuesOfferRevenue = $49. I tried something like this below but getting back no results.

DuesOfferRevenue = 

VAR LatestDuesOfferID =
IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDuesOffer")

VAR LatestDuesID =
IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDues")

RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(Activity, Activity[AMOUNT]), BLANK())

Any guidance would be greatly appreciated.

Cheers,
Ryan

[v-deddai1-msft]

Hi @Anonymous ,

Would you please explain more about the logic of 49 as total revenue for a given promotion code in your example. I failed to understand what's your need by your description.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

Apologies for missing mark first time around. I'll try to explain the $49 result better here. The data is not great, and there are two problems I need to solve. Problem 1) Identify the relevant "DUESOFFER" and "DUES" activity for each member ID. I've done this by creating a two measures. Each pulls the max SEQN number for each respective ("DUESOFFER" and "DUES") ACTIVITY_TYPE  by ID that has a THRU_DATE > Today. These two measures are in the sample data below, LatestDUESOFFER_SEQN and LatestDUES_SEQN. Problem that I'm trying to solve in this post 2) Sum the AMOUNT for the latest "DUES" activity (LatestDUES_SEQN is "1232404" in sample data) where the corresponding LatestDUESOFFER_SEQN is not null and has a UF_1 = "Vgabf2020". I can't sum on the "DUESOFFER" record AMOUNT, because it's recorded as a $0 value. I need to sum on the LatestDUES_SEQN ("1232404" in the sample data) record AMOUNT. I've reordered the sample data with the SEQN, LatestDUESOFFER_SEQN and LatestDUES_SEQN as this might make more sense. 

SEQN	LatestDUESOFFER_SEQN	LatestDUES_SEQN	ID	LAST_NAME	FIRST_NAME	PRODUCT_CODE	ACTIVITY_TYPE	THRU_DATE	TRANSACTION_DATE	UF_1	AMOUNT
1232405	1232405		900263805	Nonya	Business	ABC	DUESOFFER	9/30/2021 0:00	9/20/2020 0:00	Vgabf2020	$0
1232404		1232404	900263805	Nonya	Business	ABC-1YR	DUES	9/30/2021 0:00	9/20/2020 0:00	Approved	$49
1123159			900263805	Nonya	Business	ABC-1YR	DUES	8/31/2020 0:00	8/1/2019 0:00	Approved	$43
969559			900263805	Nonya	Business	ABC	DUESOFFER	7/31/2019 0:00	7/6/2018 0:00	WOODBOOK	$0
969557			900263805	Nonya	Business	ABC-1YR	DUES	7/31/2019 0:00	7/6/2018 0:00	Approved	$43

[v-deddai1-msft]

Hi @Anonymous ，

Would you please try the below measure?

DuesOfferRevenue = 
VAR LatestDuesOfferID =
IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDuesOffer")

VAR LatestDuesID =
IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDues")

RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(FILTER(ALL(Activity),Activity[SEQN] = LatestDuesID), Activity[AMOUNT]), BLANK())

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hello @v-deddai1-msft ,

Thank you for providing the code below. I had to edit the false return values for each IF statement as it this formula was giving:

" Calculation error in measure 'Activity'[DuesOfferRevenue]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

Here is what I changed it to, but it still returns blank. 

DuesOfferRevenue = 
VAR LatestDuesOfferID =
IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), 01)

VAR LatestDuesID =
IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), 02)

RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(FILTER(ALL(Activity),Activity[SEQN] = LatestDuesID), Activity[AMOUNT]), BLANK())

Wouldn't I need LatestDuesID to equal Activity[ID] in the FILTER? Something like:

DuesOffer Revenue = 
VAR LatestDuesOfferID = IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), 01) 
VAR LatestDuesID = IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), 02) 

RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(FILTER(ALL(Activity), [ID] = LatestDuesID), Activity[AMOUNT]), BLANK())

This also returns blank though when I run it. 

Very much appreciate your assistance, if you have any other ideas please feel free to share.

Thank you,
Ryan

[Anonymous · Accepted solution]

Not sure if my explanation was poor or it is a difficult problem issue to solve in PowerBI? But this was taking too long and there was a simplier route to a solution outside of PowerBI. My solution was create the Dues and DuesOffer relationship in SQL. Following SQL query gave me the DUES to DUESOFFER record relationship i was seeking and then imported into PowerBI using custom SQL query as 'DuesOffer2Dues' table. Then created a relationship in PowerBI model between 'DuesOffer2Dues' and 'Activity' table using the DuesOffer2Dues.D-SEQN = Activity.SEQN. 

SELECT DUES.ID, DUES.ACTIVITY_TYPE AS 'D-ACTIVITY_TYPE', DUES.SEQN AS 'D-SEQN',
	   DUESOFFER.ACTIVITY_TYPE AS 'DO-ACTIVITY_TYPE', DUESOFFER.SEQN AS 'DO-SEQN'
FROM
       (SELECT ID, ACTIVITY_TYPE, MAX(SEQN) AS SEQN
        FROM	Activity a 
        WHERE	ACTIVITY_TYPE = 'DUESOFFER' AND UF_1 = 'Vgabf2020'
		GROUP BY ID, ACTIVITY_TYPE
		) AS DUESOFFER
LEFT JOIN
       (SELECT ID, ACTIVITY_TYPE, MAX(SEQN) AS SEQN
        FROM       Activity a
        WHERE      ACTIVITY_TYPE = 'DUES'
		GROUP BY ID, ACTIVITY_TYPE
        ) AS DUES ON DUESOFFER.ID = DUES.ID