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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
peterhui50
Helper III
Helper III

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

I have a table here

peterhui50_0-1653941936181.png

 

 

All I want to do is this --

peterhui50_1-1653941982601.png

 

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

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Those are for creating calculated columns.

 

Untitled.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Those are for creating calculated columns.

 

Untitled.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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
Super User
Super User

@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]


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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