Source
r/PowerBI•
Engineer_Zero

Power BI versions of SQL's ROW_NUMBER

I've come to rely quite heavily on SQL's ability to apply an ordered rank to bins of data with relative ease, by using PARTITION BY ORDER BY. Be it ROW_NUMBER(), DENSE_RANK() etc, with one line i can establish an order to something whcih then becomes a powerful means of filtering my data when loaded into Power BI.

I've always been curious if there is a way to do this in Power BI natively? I find myself teaching others at work how to use Power BI, who don't have access or knowledge to write SQL, so then I end up writing them something to then import.

For example, determine the latest date something was inspected, per asset. in SQL, I'd write

(DENSE_RANK() OVER (PARTITION BY [the thing] ORDER BY [InspectionDate] DESC)

which I coudl then filter for '1' to show me the latest dates of the all of the things.

Thanks

Posted Jun 14, 2022, 7:14 AM UTC

610
hoonboof•

Yeah have a look at rankx, it's not a drop in replacement for window functions in sql but it fits your use case

2
Engineer_ZeroOP•

Thanks man, appreciated

1
dexterzhou•

Calculated Columns

RN =

var a='T'[Brand]

return RANKX(FILTER('T','T'[Brand]=a),'T'[CreateDate], ,DESC)

2
Engineer_ZeroOP•

Thanks! Is it possible to bin the data by more than one column? Like do I just keep making variables and then join them with &&?

1
dexterzhou•

yes,multiple variables

RN =

var country='t'[c1]

var class='t'[c2]

return

RANKX(FILTER('t','t'[c1]=country&&'t'[c2]=class),

't'[date],,desc,Dense)

1
Engineer_ZeroOP•

Love it. Thanks dude

1
Multika•

  • If you simply want the first/last/whatever value (date) grouped by some column, you can simply use this pattern:

      ADDCOLUMNS ( DISTINCT ( tbl[group_by_col] ), "MaxInspectionDate", CALCULATE ( MAX ( tbl[InspectionDate] ) ) )

  • If you want to return a (fixed) limited number of rows, you can use outer apply in SQL and there is a DAX equivalent:

      GENERATE ( <grouping_table>, CALCULATETABLE ( TOPN (n, <table_with_addition_cols>, <OrderBy_Expression>, <Order> ) )
1
Engineer_ZeroOP•

Add columns is definitely something that I haven’t really played with outside of making date time tables. I’ll check it out, thanks.

1
xRabidNomadx•

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query#:~:text=If%20you%20want%20to%20create,take%20that%20into%20account%20too.

If you want to push it farther back on the process go back to power query.

1
Engineer_ZeroOP•

Thanks! Good point re power query; I was asking about DAX but M might also be suitable.

1