Use a List of ID's from an excel to filter a dax Query connected to a SSAS Model.

[fugazzetta]

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. I could set the query to filter only the list of ID's neccesary, but this list might change everyday, and it gets tedious to change the list everyday. Is it possible extract the list of id's form the excel file on to the dax query connected to the SSAS Model so that i can filter only the values that are on that list? 

[Greg_Deckler]

@fugazzetta If you are using import or DirectQuery for Analysis Services and Power BI Datasets, you could import the ID's from the Excel file and use the IN operator in the FILTER function.

[fugazzetta]

how can I import the list of id's? Let's say users have an excel file with 5 sheets each with a different set of ID's which can change or be modified, how could I compile the whole list of ID's and dinamically use it in the dax query with the IN operator? 

[Greg_Deckler]

@fugazzetta I would use a Folder query. Put all of the Excel files into a folder and use a Folder query to import and append them all together. Are you saying that the list of ID's would be different for each user?

[fugazzetta]

@Greg_Deckler First of all thank you for repplying to all my questions! I didn't know I have to @ you, so thanks for that advice too!

Going back to my question: What I want to do is to use the list on the query itself. The SSAS Model is quite big, and I can only filter out so many other things to make the query lighter. If I could somehow use: 

---

---

in {Excel list}

Directly on my dax query I would narrow my query so much it would make it a lot faster.

I'm already doing what you suggested, I have the excel on a sharepoint and users periodically change their files, so I have the sharepoint connected to my PBI file alongside a connection to the SSAS model. The intial SSAS query gets between 10-20 million rows, but the list of id's is only 40k rows. So it's a ibg waste of processing memory to bring around 15 million rows just to filter it to 40k in power query. I would much rather have the list already in the dax query, but since the list is dynamic it's not useful to manually paste the list. 

I hope I've explained myself better this time. In summary is there a way to dynamically paste a list of id's from excel onto a dax query? and somehow create a filter on the query itself with that list rather than connecting both the excel and the ssas model to my pbi file and merging the files in power query?

[Greg_Deckler]

@fugazzetta Right, so theoretically you could construct a filter such as:

Measure =
  VAR __IDs = SELECTCOLUMNS('Excel',"ID",[ID])
  VAR __Table = FILTER('Big Fact Table', [Some Column] IN __IDs)
  <bunch of code>
RETURN
  <something>

[fugazzetta]

@Greg_Deckler this wouldn't work. I need to filter the list of id's on the dax query, not on the pbi report.

Let's say the query looks something like this:

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

       )

)

---

 Let's assume that's my dax query to get a list of id's and some details from my SSAS Model. 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 user is following, which they had 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. Let's assume it was 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?

[Greg_Deckler]

@fugazzetta No there is not. DAX is not a source system query language. That language is Power Query (M). DAX can only query data that is in the data model. So you have to bring the data into the data model from the Excel file(s) and then you can use DAX to query that data. 

What is the issue with importing the Excel files using Power Query? If you are connected Live to the SSAS cube you should be able to change that to Direct Query for Analysis Services and Power BI Datasets and then import the Excel files to create a hybrid model. If you are already importing the SSAS data, you can just add the import of the Excel files.

[fugazzetta]

@Greg_Deckler I see. The problem is that the query itself is quite heavy, it brings millions of records while I only need like 40k rows. But thanks a lot for all your input! I'mma try the composite models as you suggested. Thanks again @Greg_Deckler !