Is this possible to do this type of ranking and concatenation?

[peterhui50]

I have a table here

All I want to do is this --

I want a new calculated column call ranking and a combined column of the values.

The logic here really is a grouping of EMPLOYEE, then the TYPE, then somehow a smart ranking of the ranking column to get at the combined value.

I tried 

Ranking = COUNTX(CALCULATETABLE(File,ALLEXCEPT(File, File[EMPLID], File[Type]),File[DATE] < EARLIER(File[DATE]) ), File[Type])

but the ranking is strange..

any help is appreciated

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Those are for creating calculated columns.

Ranking CC = 
VAR _addflag =
    FILTER (
        ADDCOLUMNS (
            FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
            "@flag",
                SWITCH (
                    TRUE (),
                    VAR _mindateofemp =
                        MINX (
                            FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
                            Data[As of date]
                        )
                    RETURN
                        Data[As of date] = _mindateofemp, 1,
                    VAR _previousdate =
                        MAXX (
                            FILTER (
                                Data,
                                Data[Employee] = EARLIER ( Data[Employee] )
                                    && Data[As of date] < EARLIER ( Data[As of date] )
                            ),
                            Data[As of date]
                        )
                    VAR _previoustype =
                        MAXX (
                            FILTER (
                                Data,
                                Data[Employee] = EARLIER ( Data[Employee] )
                                    && Data[As of date] = _previousdate
                            ),
                            Data[Type]
                        )
                    RETURN
                        Data[Type] = _previoustype, 0,
                    1
                )
        ),
        Data[As of date] <= EARLIER ( Data[As of date] )
    )
RETURN
    SUMX ( _addflag, [@flag] )

Combined CC =
COMBINEVALUES ( " ", Data[Type], Data[Ranking CC] )

[peterhui50]

This worked, but is there an easier way? There must be an easier way - this is so much DAX to do something so simple. I really appreciate it, but I can't help but think there must be an easier way! 🙂

[Greg_Deckler]

@peterhui50 This might help with the ranking:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520

The 2nd column is just:

Combined = CONCATENATE([Type],[Ranking])

or

Combined = [Type] & [Ranking]