Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JB17
Frequent Visitor

Values in the table and in visual should be aligned

 

 

 

 

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 )

 

 

 

 

 

 

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

vyiruanmsft_0-1715752754420.png

Best Regards

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

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

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

 

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

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

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

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

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

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

vyiruanmsft_0-1715752754420.png

Best Regards

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors