DAX for different category of unique value.

[JB17]

Hi Everyone,

Can you help me create DAX for image below? (The image below is just an example based on the actual report). I created a slicer for each Services and System ID. Each system ID and Services is unique. The loyalty category is based on support for 5 years.

Loyal = Customers have purchased support each year in the last 5 years

Potentially Loyal = customers have purchase support 3 or 4 out of the last 5 years

Transient = customers have purchase support 2 out of the last 5 years or 1 of the last 5 years but not in the current year

New = customers have purchased support this year for the first time in 5 years.

Former & Non-Purchasing = customers have never purchased support in the last 5 years

The DAX I created for Loyalty Category is below:

Category_Measure_new =

SWITCH(

    TRUE(),

    [Category Sum of Years] = 0, "Former & Non-Purchasing",

    [Category Sum of Years] = 2, "Transient",

    [Category Sum of Years] = 5, "Loyal",

    [Category Sum of Years] IN {3, 4}, "Potentially Loyal",

    [YR2024_2] = 1, "New",

    [YR2023_2] = 1, "Transient",

    BLANK() // Default case if none of the above conditions are met

)

In the image above, System ID 11111 has 7 services, the loyalty category should be only looking at support date of each services for each year. The issue that I'm getting is when system ID has different services, the loyalty category that's showing is incorrect. 

For example in System ID 11111 in the above image, the 6th service is FPE, the customer purchased FPE this year, therefore, the loyalty category is "New", but what I'm getting is "Loyal" because instead of looking at the service the customer has purchased it is looking at all of the services for each year which is incorrect. 

I will appreciate all your help!! thanks in advance!

[v-yangliu-msft · Accepted solution]

@lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

Hi  @JB17 ,

I created some data:

Here are the steps you can follow：

1. Use Enter data – create a table.

2. Select [Service] – Column tools – Sort by column – [Index].

Because Power BI's default sorting is alphabetical, we need to create an lndex to control the sorting order

3. Create measure.

Category Sum of Years =
VAR _today =
    TODAY ()
VAR _current =
    YEAR ( _today )
VAR _last5years = _current - 4
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Year] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] >= _last5years
                && 'Table'[Year] <= _current
                && 'Table'[Services] = MAX ( 'Service_Table'[Services] )
                && 'Table'[System ID] = MAX ( 'Table'[System ID] )
        )
    )
VAR _if =
    IF ( _count = BLANK (), 0, _count )
VAR _currentcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Year] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _current
                && 'Table'[Services] = MAX ( 'Service_Table'[Services] )
                && 'Table'[System ID] = MAX ( 'Table'[System ID] )
        )
    )
VAR _column =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Services] = MAX ( 'Service_Table'[Services] )
                && 'Table'[System ID] = MAX ( 'Table'[System ID] )
        ),
        "test", _if
    )
VAR _sw =
    SWITCH (
        TRUE (),
        5 IN _column, "Loyal",
        4
            IN _column
                || 3 IN _column, "Potentially Loyal",
        OR ( 2 IN _column, 1 IN _column )
            && _currentcount = BLANK (), "Transient",
        1
            IN _column
                && _currentcount <> BLANK (), "New"
    )
RETURN
    IF ( _count = 0, "Former & Non-Purchasing", _sw )

4. Result:

If the results don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...