topic Values in the table and in visual should be aligned in DAX Commands and Tips

Values in the table and in visual should be aligned

JB17 — Sat, 18 May 2024 10:24:03 GMT

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 )

Re: Values in the table and in visual should be aligned

Anonymous — Mon, 13 May 2024 06:22:26 GMT

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

Re: Values in the table and in visual should be aligned

JB17 — Tue, 14 May 2024 06:02:12 GMT

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

Re: Values in the table and in visual should be aligned

Anonymous — Tue, 14 May 2024 07:32:27 GMT

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

Re: Values in the table and in visual should be aligned

JB17 — Sat, 18 May 2024 10:21:41 GMT

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

Re: Values in the table and in visual should be aligned

Anonymous — Wed, 15 May 2024 05:59:37 GMT

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