Values in the table and in visual should be aligned

[JB17]

hi everyone,

Can you please help me on DAX? I need to align the loyalty category in the table and in the visual:

For example in the below image, in the table, the loyalty category for a specific enterprise is showing 4 for New, I wanted to align the same loyalty category in the chart above, it should also show 4 for New, but in my example, it's showing 4 but former & non-purchasing. the visual should be looking at the Services per system ID based on its Start Date. I would really appreciate all your help. Thanks!

DAX created from suggestion:

Categ Sum of Years =

var _today=TODAY()

var _current=YEAR(_today)

var _last5years=_current-4

VAR _count =

    CALCULATE (

        COUNT ( 'SERVICE'[Year]),

        FILTER (

            ALL ( 'SERVICE' ),

            'SERVICE'[Year] >= _last5years

                && 'SERVICE'[Year] <= _current

                && 'SERVICE'[Services] = MAX ( 'Service_Table'[Services])

                && 'SERVICE'[Year] = MAX ( 'SERVICE'[Year])

        )

    )

VAR _if =

    IF ( _count = BLANK (), 0, _count )

VAR _currentcount =

    CALCULATE (

        COUNT ( 'SERVICE'[Year]),

        FILTER (

            ALL ( 'SERVICE' ),

            'SERVICE'[Year] = _current

                && 'SERVICE'[Services] = MAX ( 'Service_Table'[Services])

                && 'SERVICE'[Year] = MAX ( 'SERVICE'[Year])

        )

    )

VAR _column =

    SELECTCOLUMNS (

        FILTER (

            ALL ( 'SERVICE'),

            'SERVICE'[Services] = MAX ( 'Service_Table'[Services])

                && 'SERVICE'[Year] = MAX ( 'SERVICE'[Year])

        ),

        "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 )

[v-yiruan-msft]

Hi @JB17 ,

You can try to update the formula of calculated column as below and check if it can return the expected result.

Categ Sum of Years = 
var _today=TODAY()
var _current=YEAR(_today)
var _last5years=_current-4

VAR _count =
    CALCULATE (
        COUNT ( 'SERVICE'[Year]),
        FILTER (
            ALL ( 'SERVICE' ),
            'SERVICE'[Year] >= _last5years
                && 'SERVICE'[Year] <= _current
                && 'SERVICE'[Services] = EARLIER ( 'SERVICE'[Services])
                && 'SERVICE'[Year] = EARLIER ( 'SERVICE'[Year])
        )
    )
VAR _if =
    IF ( _count = BLANK (), 0, _count )
VAR _currentcount =
    CALCULATE (
        COUNT ( 'SERVICE'[Year]),
        FILTER (
             'SERVICE'  ,
            'SERVICE'[Year] = _current
                && 'SERVICE'[Services] =EARLIER ( 'SERVICE'[Services])
                 && 'SERVICE'[Year] =EARLIER ( 'SERVICE'[Year])
        )
)
RETURN  
    SWITCH (
        TRUE (),
      _if>=5, "Loyal",
        _if IN {3,4}, "Potentially Loyal",
       _if IN {1,2}
            && _currentcount = BLANK (), "Transient",
       _IF=1 && _currentcount <> BLANK (), "New",
       _IF=0, "Former & Non-Purchasing"
    )

If the above ones can't help you figure out, could you please provide some raw data in your table 'SERVICE' & 'Service_Table' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[JB17]

Hi v-yiruan-msft,

Thanks for helping me with DAX, however there's still an error. I attached the link below to the actual pbi file, for your review. Thank you so much for all your help! 

Segmentation.pbix

[v-yiruan-msft]

Hi @JB17 ,

Thanks for your reply. It seems like I have no access to your shared file. Could you please grant me the proper permission to it? Thank you.

Best Regards

[JB17]

please try again, let me know if you can access the link. Thanks!

[v-yiruan-msft · Accepted solution]

Hi @JB17 ,

You can update the formula of calculated column [Categ Sum of Years] as below:

Categ Sum of Years =
VAR _today =
    TODAY ()
VAR _current =
    YEAR ( _today )
VAR _last5years = _current - 4
VAR _count =
    CALCULATE (
        COUNT ( 'SERVICE'[Year] ),
        FILTER (
            ALL ( 'SERVICE' ),
            'SERVICE'[Year] >= _last5years
                && 'SERVICE'[Year] <= _current
                && 'SERVICE'[System_ID] = 'SYSTEM'[System_ID]
                && 'SERVICE'[System_SureServices_Key] = SYSTEM[System_SureServices_Key]
                && 'SERVICE'[SureService_Status] = "Current"
        )
    )
VAR _if =
    IF ( _count = BLANK (), 0, _count )
VAR _currentcount =
    CALCULATE (
        COUNT ( 'SERVICE'[Year] ),
        FILTER (
            'SERVICE',
            'SERVICE'[Year] = _current
                && 'SERVICE'[System_ID] = 'SYSTEM'[System_ID]
                && 'SERVICE'[System_SureServices_Key] = 'SYSTEM'[System_SureServices_Key]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _if >= 5, "Loyal",
        _if IN { 3, 4 }, "Potentially Loyal",
        _if
            IN { 1, 2 }
                && _currentcount = BLANK (), "Transient",
        _IF = 1
            && _currentcount <> BLANK (), "New",
        _IF = 0, "Former & Non-Purchasing"
    )

Best Regards