Filtering a dax query for ceratin ID's using an excel file

Question

At work we have an SSAS Tabular Model with different tables. I usually connect to it via Power BI using dax queries on import mode. Right now I need to create a dax query to analize a list of certain ID's which other users have on some excel files in a company sharepoint.

Let's say the query looks something like this (this is a very simplified version of what i'm doing, but I think it's enought o understand):

Evaluate(
    Summarize(
          'Sales',
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          "TotalSales", sumx(filter('Sales', 'DimProductCategory'[Category]="Snacks"), [SalesTotal])
       )

)

The thing is there might be 100 million rows for the category snacks, and i only want to analize the list of ID's that my users are following, which they have on an excel file. That list of ID's changes every day, so it's not as easy as making a filter with the straight list. If it was just a list of 10 id's i would simply create the following var:

Var __filterIDS=
          treatas(
               {1,2,3,4,5,6,7,8,9,10}, 'ProductTable'[ProductID])

Then I can just add that filter to my query and it would look like this:

Define
Var __filterIDS=
          treatas(
               {1,2,3,4,5,6,7,8,9,10}, 'ProductTable'[ProductID])

var __Query=

Summarize(
          'Sales',
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          __filterIDS,
          "TotalSales", sumx(filter('Sales', 'DimProductCategory'[Category]="Snacks"), [SalesTotal])

       )

)

Evaluate
__Query

But, as I explained earlier, the list of id's is dynamic, plus is a lot bigger than just 10 numbers, it might be 4000 id's or even lots more. So, is there a way to somehow get the list of id's from an excel in the dax query?

Assuming there's a way i would do something like:


Var __filterIDS=
          treatas(
               {list from excel}, 'ProductTable'[ProductID])

What i've been doing so far is just import the excel files to the PBI file and merge the id's with my query to the model to filter out the lsit of neccesary ID's. Dax query might be 100 million records but all I want is that list of ID's from the excel files, so it would make a lot more sense to filter the list on the query itself before entering to power bi.

Sam Nseir · Accepted Answer · 2023-10-19 04:54:52Z

0

You can import your Excel table into PowerQuery - then you can use the following to transform a list/column into a comma-separated string of values:

let
  Source = Combiner.CombineTextByDelimiter(",")(#"your excel table"[column])
in
  Source

You can then use this string to inject into your DAX in PowerQuery Advanced Editor (see bold below)

AnalysisServices.Database("...", "...", [Query="Define#(lf)Var __filterIDS=#(lf) treatas(#(lf) {" & csvIDs & "}, 'ProductTable'[ProductID])#(lf)#(lf)var __Query=#(lf)#(lf)Summarize(#(lf) 'Sales',#(lf) 'ProductTable'[ProductID],#(lf) 'ProductTable'[ProductName],#(lf) 'DimProductCategory'[CategoryName],#(lf) 'DimDate'[CalendarDate],#(lf) __filterIDS,#(lf) ""TotalSales"", sumx(filter('Sales', 'DimProductCategory'[Category]=""Snacks""), [SalesTotal])#(lf)#(lf) )#(lf)#(lf))#(lf)#(lf)Evaluate#(lf)__Query", Implementation="2.0"])

Lastly, you should consider using a Composite Model in Power BI. Import the IDs from Excel, and Direct Query based on a relationship to your excel table to SSAS. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

answered Oct 19, 2023 at 4:54

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fugazzetta Over a year ago

Hey! Thanks a lot for your response, i'll be reading more into composite models, sounds interesting. How do I "dynamically" inject the bold part in your code? would " & csvIDs & " be the name of the query containing the string of id's separated with a comma?

Sam Nseir Over a year ago

Yes that's right. csvIDs is the name of the query containing the IDs. To add it in your dax, you will need to go into the Advanced Editor of the query that has your dax in it. It will look very similar to what I have above in the answer.

Fugazzetta Over a year ago

I'm gonna give it a try as soon as I get some free time today. This sounds a lot better than importing the whole excel and the model and then merging both queries to keep only the list of ids. Thanks for your input!

Fugazzetta Over a year ago

Hey Sam, I finally got a chance to test this, it didn't work as expected. It prompted an error "Query 'Query1' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination". Any clue?

Sam Nseir Over a year ago

This is related to Privacy Settings. Check to ensure that each one is set to same level: learn.microsoft.com/en-us/power-bi/enterprise/… .

Collectives™ on Stack Overflow

Filtering a dax query for ceratin ID's using an excel file

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related