Syntax for using SAMPLE() function with two or more orderBy expressions

0
votes

I would like to use the SAMPLE() function in DAX to select random rows from a table. In the documentation the function appears to support multiple OrderBy_Selection parameters but I'm unable to get the syntax right.

I have a table named dCRQRisk with two columns that I would like to order by before selecting the sample rows: RSO, then RISK_LEVEL, both in Ascending order.

//This is the syntax 

SAMPLE(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])`

//This works

SAMPLE(31,dCRQRisk, dCRQRisk[RSO],1)

//When I try to add the second OrderBy_Expression it does not work 

SAMPLE(31, dCRQRisk, dCRQRisk[RSO],[1[,dCRQRisk[RISK_LEVEL],[1]]])

//This is the error message

Query(2, 60) Unexpected value for ORDER argument in SAMPLE function. Use 0/FALSE/DESC for descending order or 1/TRUE/ASC for ascending order.

1
randomdaxpowerpivot

1 Answers

1
votes

Those brackets are to indicate optional arguments, not part of the syntax.

Try this:

SAMPLE(31, dCRQRisk, dCRQRisk[RSO], 1, dCRQRisk[RISK_LEVEL], 1)