Forum Discussion
Analysis Services Import DAX Query filtered on a previous Import
Hello,
I am looking for a way to import to my Power BI Desktop data from a SQL Server Analysis Services DB filtered based on a previously imported data from another data source.
Sample scenario:
- I have an excel file with one column that contains account IDs named "ID", I import that excel file and now I have a table in my PBI named account with only one column named IDs.
- I now want to import from my Analysis Service only the accounts with IDs present in my table "account".
- The following query works very well as intended, but I want to replace the {1, 2, 3, 4} with something like account[ID] (account being my existing table in my model).
Thank you in advance.
Hi,
If you get your list imported as a table you can use the advanced power query editor to convert it to a string:
let Source = YourExcelSource, ChangeType= Table.TransformColumnTypes(Source,{{"Academy", type text}}), OutputList = Table.ToList ( ChangedType ), OutputString = "{""" & Text.Combine(OutputList,""",""") & """}" in OutputStringThe above deals with strings hence the double "" to escape them. This returns a string which we'll reference:
If you then use advanced editor to alter your analysis service query you can reference the string like this:
If you need specific to your example I can try and rewrite.
2 Replies
- bcdobbs
Community Champion
Hi,
If you get your list imported as a table you can use the advanced power query editor to convert it to a string:
let Source = YourExcelSource, ChangeType= Table.TransformColumnTypes(Source,{{"Academy", type text}}), OutputList = Table.ToList ( ChangedType ), OutputString = "{""" & Text.Combine(OutputList,""",""") & """}" in OutputStringThe above deals with strings hence the double "" to escape them. This returns a string which we'll reference:
If you then use advanced editor to alter your analysis service query you can reference the string like this:
If you need specific to your example I can try and rewrite.
