Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Filter for values ending in Zero

Dear.

I would appreciate your help in the following consultation:

I need to filter the values ending in three zeros for the following DAX formula:

Movimientos = CALCULATE(MIN(MB51_TODO[Fe.contab.]),

MB51_TODO[CMv]="101" || MB51_TODO[CMv]="501" ||

MB51_TODO[CMv]="201" || MB51_TODO[CMv]="202" ||

MB51_TODO[CMv]="221" || MB51_TODO[Ce.] ="1000")

I need a filter on the following argument:

MB51_TODO[Ce.] ="1000") Only values ending in "000" need to be filtered.

Also, if there were any ideas for reducing the length of my formula, I would appreciate it.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi JMOLLAN ,

    You can update the formula of the measure [Movimientos] as below:

    Movimientos =
CALCULATE (
    MIN ( MB51_TODO[Fe.contab.] ),
    FILTER (
        MB51_TODO,
        MB51_TODO[CMv]
            IN { "101", "501", "201", "202", "221" }
                || RIGHT ( MB51_TODO[Ce.], 3 ) = "000"
    )
)

    Best Regards

2 Replies

  • LQuedas's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    Hey Syndicate_Admin ,

     

    Can you try the RIGHT function to check the last 3 chars? e.g. 

    RIGHT(MB51_TODO[Ce.],3)="000"

     

    Movimientos = CALCULATE(MIN(MB51_TODO[Fe.contab.]),

    MB51_TODO[CMv]="101" || MB51_TODO[CMv]="501" ||

    MB51_TODO[CMv]="201" || MB51_TODO[CMv]="202" ||

    MB51_TODO[CMv]="221" || RIGHT(MB51_TODO[Ce.],3)="000")

     

    Cheers, LQ

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi JMOLLAN ,

    You can update the formula of the measure [Movimientos] as below:

    Movimientos =
CALCULATE (
    MIN ( MB51_TODO[Fe.contab.] ),
    FILTER (
        MB51_TODO,
        MB51_TODO[CMv]
            IN { "101", "501", "201", "202", "221" }
                || RIGHT ( MB51_TODO[Ce.], 3 ) = "000"
    )
)

    Best Regards