Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 period | BU | description | amount |
201901 | 1 | opex | 13334 |
201901 | 2 | opex | 19651 |
201901 | 3 | opex | 5452 |
201901 | 4 | opex | 5903 |
201901 | 1 | capex | 15264 |
201901 | 2 | capex | 29237 |
201901 | 3 | capex | 41741 |
201901 | 4 | capex | 25605 |
201902 | 1 | opex | 42181 |
201902 | 2 | opex | 35783 |
201902 | 3 | opex | 18992 |
201902 | 4 | opex | 32995 |
201902 | 1 | capex | 1492 |
201902 | 2 | capex | 32536 |
201902 | 3 | capex | 4393 |
201902 | 4 | capex | 35412 |
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.
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 period | BU | description | amount | Rank |
201901 | 1 | opex | 13334 | 2 |
201901 | 2 | opex | 19651 | 1 |
201901 | 3 | opex | 5452 | 4 |
201901 | 4 | opex | 5903 | 3 |
201901 | 1 | capex | 15264 | 4 |
201901 | 2 | capex | 29237 | 2 |
201901 | 3 | capex | 41741 | 1 |
201901 | 4 | capex | 25605 | 3 |
201902 | 1 | opex | 42181 | 1 |
201902 | 2 | opex | 35783 | 2 |
201902 | 3 | opex | 18992 | 3 |
201902 | 4 | opex | 32995 | 4 |
201902 | 1 | capex | 1492 | 4 |
201902 | 2 | capex | 32536 | 2 |
201902 | 3 | capex | 4393 | 3 |
201902 | 4 | capex | 35412 | 1 |
Desired output as VAR table
If anyone can please help would be great. Thank you in advance.
Solved! Go to Solution.
@v-yuta-msft figured out. I was missing ADDCOLUMNS in the first place.
@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.
@v-yuta-msft figured out. I was missing ADDCOLUMNS in the first place.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |