I have a excel file connected to a power bi semantic model, and loaded the model to a table. I have added filters on a date colum and company code.
What I want to do is dynamically change the filters by the user input.
I have a sheet that the end user will input the start, end date and companies code , these are named ranges.
Is it possible to modify the dax query so that I can insert these values into the dax code that is under the command text box where the dax code sits.
I have achieved this via power query but then I have to give the end user access to the dB object.
Was wondering if the dax code can be written dynamically so can pass values into the filter part.
I could copy the code into a blank sheet and convert to a string and reference the values and then use a macro to copy it back into the properties and run the command but don't really want to do this
No, you can't connect DAX to a cell in the spreadsheet. It can only reference data in the data model.
@mynda makes sense, when you say data model you mean the semantic model, not the data model in excel.
Iv got a way to do by having the dax code copied to a hidden sheet and string it with cell info, vba will just copy it back to the code section and execute. Will try on Monday.
Also when I change the dax to sql and select sql in the box above and write an sql statment it says it not supported by the just wondering if you know why.
Finally thanks for your reply, my first ever message and been following you guys since 2013. Really helped me when I was a performance analyst in asset management.
Yes, sorry, I meant semantic model.
I wonder why you don't just use Power Query in Excel to get the data direct from the source then load to the Excel data model/Power Pivot. i.e. skip Power BI.
That way you can use Power Query in Excel to reference a cell in the Excel file and use that as a parameter to filter the query 🤔
Great to hear you've found our content helpful over the years 🙏
@mynda I have already done it this way. Power query takes the values and runs a select * with where clause on the values passed from excel.
What we want to do is pass this excel file to the finance team to run it themselves and will have to give them access to the back end azure lake. Not really a big issue I just thought if I could create a power bi dataset and connect to it then I can control the access from the semantic model side.
