Need to return the attribute for the min date of current filter context

Anonymous — Thu, 02 May 2019 00:15:09 GMT

I have a Date table, a Customer table and a Sales table. Date and Customer are one-to-many to Sales based on Calendar Date and Customer ID. I need to build a measure that counts distinct customer IDs based on a Sales attribute (segment). However, each Customer ID must only be counted in one segment - the segment that goes with their first/earliest Sales Date based on the current date filter context. I have tried and tried to no avail. I thought the code below might work, but my SSAS Tabular model doesn't support the TREATAS function and I wasn't sure how to adapt. And I may be completely off track. Any help is much appreciated!!!

MarcoTreatAsAlgorithm = 
CALCULATE (
    // Computes table containing last transaction for each Customer_id (or whatever ID of interest)
    // in the current filter context:
    VAR LastCustomerTransactionDate =
        ADDCOLUMNS (
            VALUES ( CustomerTransactions[Customer_id] ),
            "MaxFeatureTransNum", CALCULATE ( MAX ( CustomerTransactions[Transaction_num] ) )
        )
    // Use the above VAR to establish filter context using TREATAS and computes
    // the last activity status for the MAX(Transaction_num]) in the same date context:
    VAR LastCustomerTransactionState =
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( CustomerTransactions[Customer_id] ),
                "MaxCustomerActivityCode_id", CALCULATE ( MAX ( CustomerTransactions[CustomerActivityCode_id] ) )
            ),
            TREATAS (
                LastCustomerTransactionDate,
                CustomerTransactions[Customer_id],
                CustomerTransactions[Transaction_num]
            )
        )
    // Count records from the above table where the last status 
    // within the current date context is 2 (change "2" to whatever filter value you need):
    VAR Result =
        COUNTROWS (
            FILTER ( LastCustomerTransactionState, [MaxCustomerActivityCode_id] = 2 )
        )
    RETURN
        Result,
    USERELATIONSHIP ( PBIDate[Date], CustomerTransactions[TransactionAddedDate] )
)

Re: Need to return the attribute for the min date of current filter context

technolog — Sat, 03 Feb 2024 17:33:32 GMT

To address your requirement of counting distinct customer IDs in a specific segment based on their first/earliest Sales Date within the current date filter context, you need to create a measure in Power BI that accomplishes a few key tasks: identifying the earliest sales date for each customer, determining the segment for that earliest date, and then counting distinct customers per segment. The solution involves a combination of DAX functions and careful consideration of the relationships and filter contexts in your data model.

Since your SSAS Tabular model does not support the TREATAS function, you'll need to use an alternative approach. Here's a revised DAX measure that should help you achieve your objective:

CustomerSegmentCount =
VAR EarliestSalesPerCustomer =
CALCULATETABLE(
SUMMARIZE(
Sales,
Sales[Customer ID],
"EarliestSaleDate", MIN(Sales[Sales Date])
),
REMOVEFILTERS(Date)
)
VAR CustomerSegmentAtEarliestSale =
SUMMARIZE(
EarliestSalesPerCustomer,
Sales[Customer ID],
"Segment", CALCULATE(
MAX(Sales[Segment]),
USERELATIONSHIP(Date[Calendar Date], Sales[Sales Date]),
Sales[Sales Date] = EARLIER(EarliestSalesPerCustomer[EarliestSaleDate])
)
)
RETURN
COUNTROWS(CustomerSegmentAtEarliestSale)

topic Need to return the attribute for the min date of current filter context in DAX Commands and Tips

Need to return the attribute for the min date of current filter context

Re: Need to return the attribute for the min date of current filter context