Order by with selectcolumns. Using in SSAS Tabular

[computermike]

I have a selectcolumns statement that works well in SSAS Tabular as "Detail Rows Expresion".  I have two issues.

1.) If I put my expression in DAX Studio it doesn't run, so its hard to develope.

2.) I need to add an Order by to my selectColumns, to make my drilltrhough better for my end users.

Here is my DAX used in my Tabular project below.  Any help appreciated with either issue.

Thanks, Mike

SelectColumns(
'fSales',
"Document No", Related(dInvoice[InvDocNum]),
"Line No", Related(dInvoice[DocLineNum]),
"Sub Line No", Related(dInvoice[DocLineSubNum]),
"Order date",Related(dDate[Date]),
"Sales", [SalesAmt],
"Accruals", [AccrualAmt],
"CrM", [CrMemoAmt],
"Other", [OtherAmt],
"Total",[Amount]
)

[Anonymous]

@computermike,

It is not supported to drill through from a measure in Power BI Desktop. In Power BI Desktop, drillthrough is based on dimension fields, for more details, please review this article.

In addition, after you import required tables from SSAS to Power BI Desktop, create a new table using above DAX , the DAX should run as expected.



Regards,
Lydia

[computermike]

I am creating reports in Excel first, so its the drill through in Excel that I am trying to get to work.  I posted my issue here because I was under the impression this is the best place for DAX questions.

I am going to try creating a caculate table in SSAS or adding the related feilds to my fact table or determining what is causing the sort.  I may also add a column for sorting to my fact table.

yes, this work in SSMS and dax studio, but returns an error in SSAS Tabular.  If I remove the order by clause it works in SSAS Tabular.

evaluate
SELECTCOLUMNS (
'fSales',
"Document No", RELATED ( dInvoice[InvDocNum] ),
"Line No", RELATED ( dInvoice[DocLineNum] ),
"Sub Line No", RELATED ( dInvoice[DocLineSubNum] ),
"Order date", RELATED ( dDate[Date] ),
"Sales", [SalesAmt],
"Accruals", [AccrualAmt],
"CrM", [CrMemoAmt],
"Other", [OtherAmt],
"Total", [Amount]
) order by [Line No] desc

[Anonymous]

@computermike,

As the question is more related to SSAS, I would recommend you post the question in the SSAS forum at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices  . It is appropriate and more experts will assist you.



Regards,
Lydia

[computermike · Accepted solution]

I posted my question over at the SSAS forum and got some help from Darren Gosbell.  If you see anything by him read, he's great.  He suggested I wrap in TOPN.  I havn't had time to implement yet except in SSMS as a query.  I did notice the sorting is not guarentteed according to the documentation.  Here my link to the SSAS thread.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6778d306-53f7-4e3a-b890-38bcf58d89f2/2017-d...