Search code examples
listfilterpowerbidaxssas-tabular

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

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.

Solution

  • 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