Dynamic Product Name Combinations and Display for Each Customer Using DAX in Power BI

[Lopez0090]

■Goal:

Create a DAX to dynamically combine the Items for each CustomerID and display each combination of Items and its proportion (count of CustomerID).

■What has been achieved:
・Creation of a DAX that dynamically combines the Items for each CustomerID.

・The combination changes based on the slicers (YYYYMM, Country).

For example, for CustomerID "26087", when no slicer is selected, it displays "Bicycle/Car/Motorcycle". However, when the YYYYMM slicer is set to "2019", it becomes "Car/Motorcycle".

■Main Issues:

・With the DAX created, if no CustomerID is selected, it displays the combination for all CustomerIDs.

When no slicer is selected, I would like to see a list of combinations for each CustomerID, like the following example:

DAX being used:

Measure = 
VAR CurrentCustomerID = VALUES('Table'[CustomerID])
VAR SelectedYear = VALUES('Table'[YYYYMM]) 
VAR SelectedCountry = VALUES('Table'[Country])

VAR ProductList = 
    CALCULATETABLE(
        VALUES('Table'[Items]),
        'Table'[CustomerID] IN CurrentCustomerID,
        'Table'[YYYYMM] IN SelectedYear,
        'Table'[Country] IN SelectedCountry
    )

VAR SortedDistinctProductList = 
    CONCATENATEX(
        ProductList, 
        'Table'[Items], 
        "/", 
        'Table'[Items], 
        ASC
    )

RETURN SortedDistinctProductList

Data source (Please use in Power BI's Query Editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZIxT8QwDIX/Cup8g+2mvWTlNhAswHS6IVwiqDhSVCoh/j1tiJuodykVk9/wvth+8X5fUCWw2BQERFAFMZRR3ugP7Ya6011x2KRWBBHEUMSiFdgKf1pLtpb/f/Wu7dvu+H08WU8ISYoJigQlxHUT7bUQ0rtQIgQxFK/vbf9qu5N25jNHESFnOVJ4Rs2mQ6J6G4IM/XyiXj/q5utswIQATBbyeiLybYhToJDCCmahEX9MdKMCDGJ0p+aHN93b51Z35pepgA8OJxEu78k1vTVXt417Me37PIMFcKedNvpSM5Q1/ywmP4uZS1gFzDKjSkkRrGEwz1zcKN50Hpq2mcw1yC2by2guM1skfsUHrfjAclusYfxAhx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, YYYYMM = _t, Year = _t, Month = _t, Country = _t, Items = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"YYYYMM", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Country", type text}, {"Items", type text}})
in
    #"Changed Type"

Would someone be able to help me think this through?

I appreciate your assistance in advance.

Best regrds,

[Lopez0090]

@v-zhangti 

Thank you very much! I could solve by your ansewer.

[v-zhangti · Accepted solution]

Hi, @Lopez0090 

You can try the following methods.
Column:

Item Column = CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Table'[Items] ),
        FILTER ( ALL ( 'Table' ),
            [CustomerID] = EARLIER ( 'Table'[CustomerID] ) ) ),
    [Items],
    "/"
)

Measure:

CustomerID_Count = CALCULATE(COUNT('Table'[Item Column]),ALLEXCEPT('Table','Table'[CustomerID],'Table'[Item Column],'Table'[Year]))

Your original Measure needs no change.

Please see the attached.

Best Regards,

Community Support Team _Charlotte

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