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
SNRCON81
New Member

RANKX Over 3 Fields. ID, DATE, TIME & Direction

Hi All,

 

First post, hoping you can help me 🙂 

I'm writing some DAX code (Using the DAX query window to test)  to create a table that will give me a rank over my ID, Date, Time and direction fields

Sample Data...

Sheet1[Id][Date][Time]Sheet1[Direction]Sheet1[RANK][NEW RANK]
101/05/202407:45:00In11
101/05/202409:45:00In22
101/05/202410:46:00Out11
101/05/202411:02:00Out22
101/05/202412:02:00Out33
101/05/202413:00:00In13
101/05/202413:00:00Out14
101/05/202413:02:00In14
101/05/202415:05:00Out15
101/05/202418:33:00In15
201/05/202410:25:00Out11
201/05/202413:25:00Out22
201/05/202415:33:00In11
201/05/202416:00:00In22
201/05/202416:55:00In33
201/05/202417:21:00Out13
201/05/202418:00:00Out24
201/05/202419:33:00Out35
201/05/202419:34:00In14
201/05/202420:00:00In25

 

 

Below is my code

 

EVALUATE

SELECTCOLUMNS(
	Sheet1,
	Sheet1[Id],
	"Date",FORMAT(Sheet1[Date],"dd/mm/yyyy"),
	"Time",FORMAT(Sheet1[Time],"hh:mm:ss"),
	Sheet1[Direction],
	Sheet1[RANK],
	"NEW RANK",
	
RANKX(
    FILTER(
        Sheet1,
        Sheet1[Id] = EARLIER(Sheet1[Id]) &&
        Sheet1[Date] = EARLIER(Sheet1[Date]) &&
        Sheet1[Direction] = EARLIER(Sheet1[Direction])
    ),
    Sheet1[Time],
    ,
    ASC,
    DENSE
))

 

 

I'm getting the "NEW RANK" field and I want the 'Sheet1'[Rank] field (I just wrote this out in Excel)

The first 2 groups of "In" and "Out" work well but row 6 goes to 3 and does not reset back to 1 as desired

 

Feels like I’m close , any help would be greatly appreciated 🙂

 

Many thanks!

 

1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

Hi @SNRCON81 ,

 

You can try below dax query.

 

xifeng_L_0-1715941108328.png

 

EVALUATE
VAR vTb1 = 
	SELECTCOLUMNS(
		Sheet1,
		Sheet1[Id],
		"Date",FORMAT(Sheet1[Date],"dd/mm/yyyy"),
		"Time",FORMAT(Sheet1[Time],"hh:mm:ss"),
		"Index",ROUND(Sheet1[Date]+Sheet1[Time],4)&'Sheet1'[direction],
		Sheet1[Direction],
		Sheet1[RANK]
	)
VAR vTb2 = 
	ADDCOLUMNS(
		vTb1,
		"Grp",
			SUMX(
				FILTER(vTb1,[Index]<=EARLIER([Index]) && 'Sheet1'[id]=EARLIER('Sheet1'[id])),
				VAR CurId = 'Sheet1'[id]
				VAR CurIndex = [Index]
				VAR CurDirection = 'Sheet1'[direction]
				VAR PreDirection = 
					MAXX(
						TOPN(1,FILTER(vTb1,'Sheet1'[id]=CurId && [Index]<CurIndex),[Index]),
						'Sheet1'[direction]
					)
				RETURN
				IF(CurDirection<>PreDirection,1,0)
			)
	)
RETURN
ADDCOLUMNS(
	vTb2,
	"NEW RANK",
		RANKX(
			FILTER(vTb2,'Sheet1'[id]=EARLIER('Sheet1'[id]) && [Grp]=EARLIER([Grp])),
			[Time],,ASC,Dense
		)
)
	

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

 

View solution in original post

2 REPLIES 2
xifeng_L
Solution Sage
Solution Sage

Hi @SNRCON81 ,

 

You can try below dax query.

 

xifeng_L_0-1715941108328.png

 

EVALUATE
VAR vTb1 = 
	SELECTCOLUMNS(
		Sheet1,
		Sheet1[Id],
		"Date",FORMAT(Sheet1[Date],"dd/mm/yyyy"),
		"Time",FORMAT(Sheet1[Time],"hh:mm:ss"),
		"Index",ROUND(Sheet1[Date]+Sheet1[Time],4)&'Sheet1'[direction],
		Sheet1[Direction],
		Sheet1[RANK]
	)
VAR vTb2 = 
	ADDCOLUMNS(
		vTb1,
		"Grp",
			SUMX(
				FILTER(vTb1,[Index]<=EARLIER([Index]) && 'Sheet1'[id]=EARLIER('Sheet1'[id])),
				VAR CurId = 'Sheet1'[id]
				VAR CurIndex = [Index]
				VAR CurDirection = 'Sheet1'[direction]
				VAR PreDirection = 
					MAXX(
						TOPN(1,FILTER(vTb1,'Sheet1'[id]=CurId && [Index]<CurIndex),[Index]),
						'Sheet1'[direction]
					)
				RETURN
				IF(CurDirection<>PreDirection,1,0)
			)
	)
RETURN
ADDCOLUMNS(
	vTb2,
	"NEW RANK",
		RANKX(
			FILTER(vTb2,'Sheet1'[id]=EARLIER('Sheet1'[id]) && [Grp]=EARLIER([Grp])),
			[Time],,ASC,Dense
		)
)
	

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

 

Excellent!

I've Just tested my proof of concept locally and it worked like a charm

I need to re-produce on the client side and test on a wider dataset, but the solution here works as intended with the above info provided 🙂
Thanks again.

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.