Use a measure like a filter

[Giulia997]

I have calculated a measure (in DAX), the measure Main Code extrct the first letter from a column table.

I need this measure in a filter. I think this formula can be simplify... 

I need to do everything in DAX, i can't edit the original dataset

Main Code =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('DIM_DOWNTIME'[Downtime Code])

VAR __MAX_VALUES_TO_SHOW = 1

RETURN

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW,

                    VALUES('DIM_DOWNTIME'[Downtime Code]),

                    LEFT('DIM_DOWNTIME'[Downtime Code],1),

                    ASC

                ),

                LEFT('DIM_DOWNTIME'[Downtime Code],1),

                ", ",

                LEFT('DIM_DOWNTIME'[Downtime Code],1),

                ASC

            ),

            ",  ecc."

        ),

        CONCATENATEX(

            VALUES('DIM_DOWNTIME'[Downtime Code]),

            LEFT('DIM_DOWNTIME'[Downtime Code],1),

            ", ",

            LEFT('DIM_DOWNTIME'[Downtime Code],1),

            ASC

        )

    )

[Anonymous · Accepted solution]

Hi @Giulia997 ,

 You can modify your formula like below:

Main Code1 = 
VAR __MAX_VALUES_TO_SHOW = 1
VAR __VALUES_TO_SHOW =
    TOPN (
        __MAX_VALUES_TO_SHOW,
        VALUES ( 'DIM_DOWNTIME'[Downtime Code] ),
        LEFT ( 'DIM_DOWNTIME'[Downtime Code], 1 ), ASC
    )
VAR __FIRST_LETTERS =
    CONCATENATEX (
        __VALUES_TO_SHOW,
        LEFT ( 'DIM_DOWNTIME'[Downtime Code], 1 ),
        ", "
    )
RETURN
    IF (
        DISTINCTCOUNT ( 'DIM_DOWNTIME'[Downtime Code] ) > __MAX_VALUES_TO_SHOW,
        __FIRST_LETTERS & ", ecc.",
        __FIRST_LETTERS
    )

Best Regards,
Adamk Kong

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

[bhanu_gautam]

@Giulia997 , Try this

Main Code =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('DIM_DOWNTIME'[Downtime Code])
VAR __MAX_VALUES_TO_SHOW = 1
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('DIM_DOWNTIME'[Downtime Code]),
LEFT('DIM_DOWNTIME'[Downtime Code], 1),
ASC
),
LEFT('DIM_DOWNTIME'[Downtime Code], 1),
", "
),
", ecc."
),
CONCATENATEX(
VALUES('DIM_DOWNTIME'[Downtime Code]),
LEFT('DIM_DOWNTIME'[Downtime Code], 1),
", "
)
)

[Giulia997]

I have this result