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

how to rank by subcategory in a DAX VAR (temp) table

Hello experts,

 

I am currently doing a DAX query and let's suppose at a certain step (VAR_3) I generate the follwoing table

 

 

 

Table=
VAR _1 =.....
VAR _2=....
VAR _3=....
Return _3

 

 

 

 

fiscal periodBUdescriptionamount
2019011opex13334
2019012opex19651
2019013opex5452
2019014opex5903
2019011capex15264
2019012capex29237
2019013capex41741
2019014capex25605
2019021opex42181
2019022opex35783
2019023opex18992
2019024opex32995
2019021capex1492
2019022capex32536
2019023capex4393
2019024capex35412

 

Now is there a way for me to rank the amount by fiscal period-description group for VAR _3 table in a VAR_4 variable.

 

I can easily do this on a physical table for calculated column as following and I am not looking for that here.

Capture.PNG

I want to write a code which returns the same output as above in a VAR _4 table so that I can utilize that in the subsequent query. I tried to replicate the above code on a temp table and it did not work.

 

fiscal periodBUdescriptionamountRank
2019011opex133342
2019012opex196511
2019013opex54524
2019014opex59033
2019011capex152644
2019012capex292372
2019013capex417411
2019014capex256053
2019021opex421811
2019022opex357832
2019023opex189923
2019024opex329954
2019021capex14924
2019022capex325362
2019023capex43933
2019024capex354121

 

Desired output as VAR table

 

If anyone can please help would be great. Thank you in advance.

@Ashish_Mathur

@Zubair_Muhammad 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION

cx.PNG

@v-yuta-msft  figured out. I was missing ADDCOLUMNS in the first place.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@smpa01 ,

 

I'm not very clear about your requirement, are you going to create a virtual table which contains the rank column using other variable tables in dax? If so, you may try GROUPBY() and CURRENTGROUP().

GROUPBY (  
Sales,   
Geography[Country],   
Product[Category],   
“Total Sales”, SUMX( CURRENTGROUP(), Sales[Price] * Sales[Qty])  
)  

 

Community Support Team _ Jimmy Tao

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

 

 

cx.PNG

@v-yuta-msft  figured out. I was missing ADDCOLUMNS in the first place.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@v-yuta-msft  sorry for the confusion.

 

A simplified version of my requirement is how to apply SQL's equivalent of ROW_NUMBER to a DAX sub query?

ROW_NUMBER() OVER(PARTITION BY xxx, yyy ORDER BY xxx ASC) 

  Particularly, in my example let's suppose your raw data is

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY8xDsMwCEXv4jmD4YNtzhJlqKrO7Zjjp5YaB+oMSEhP8PTWNXEmy5SW1Of9ee19BSBpWxxlR60oRYqLqihHKA5aRoR9no/fY+Vyoz0xG6PO3hMLVaHZPK61ZL0wx2JhahSpK4bWhkhdMTUzjtQlg81uvKNZ/m99MlhRZvFIhmEWj2MV+v7eDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"fiscal period" = _t, BU = _t, decription = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"fiscal period", Int64.Type}, {"BU", Int64.Type}, {"decription", type text}, {"amount", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"decription", "description"}})
in
    #"Renamed Columns"

 

 

 

Now you are trying to do a DAX query and the first line is following

 

Table 2 = 
VAR _1 = SELECTCOLUMNS('Table',"fiscal period",[fiscal period]+0,"BU",[BU]+0,"description",[description]&"","amount",[amount]+0)
RETURN _1

 

 

How can you create a subquery in this which gives you the ranking of the BU by Amount descending within each fiscal period-description grouping so that it creates a VAR _2 table with my desired output. I need the ranking in a subquery so that I can keep on utilizing that table for further complex requirement. Please let me know if this is still not clear. If there is a solution available to this I can apply that to my current scenario.

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.