Forum Discussion
User Input in DAX Query
Hello,
I have a project set up that is pulling in data from an accounting system. Right now I am pulling in data for "2023_Scenario_1" which is hard coded in my DAX query. I want to be able to have a user select this scenario and have it push that selection into this query. I tried created a new paramater called"Selected Scenario" and I replaced the "2023_Scenario_1" below with @Selected_Scenario but I got an error that this paramter wasn't declared.
EVALUATE
SUMMARIZECOLUMNS (
'Organization'[Asset Final Desc],
'Organization'[Asset Desc],
'FactFinance'[DimCompanyID],
'FactFinance'[DimScenarioID],
'DateGL'[YYYYMM],
'FactFinance'[DimProductID],
'ProjectType'[Project Type Level 1 Name],
FILTER(FactFinance, FactFinance[DimScenarioID] IN {"2023_Scenario_1"}),
FILTER(Versions, Versions[Versions] IN {"Final"}),
FILTER(DateGL, DateGL[YYYYMM] >= "202310"),
FILTER(DateGL, DateGL[Balance Type] IN {"Monthly Balance"}),
FILTER(GrossNet, GrossNet[Gross-Net] IN {"Net"}),
"Total Expense", [Total Expense],
"Revenue", [Revenue],
"Royalties", [Royalties],
)
I've taught 10s of thousands of people DAX. I've seen it all. Normally when I see this approach (DAX queries) it comes from someone that has a SQL background. Generally speaking, this is not how you should approach power BI. Instead do the following
- Prepare your data in a star schema
- Load all the data you will need for all reports. You should never have to refresh the report based on different user data needs
- add slicers to the page to apply filters
- forget about writing queries. Instead use the visuals (start with table and matrix) to visually build the output you need. The visual creates the structure/group bys, etc, and measures are replacements for aggregations.
1 Reply
I've taught 10s of thousands of people DAX. I've seen it all. Normally when I see this approach (DAX queries) it comes from someone that has a SQL background. Generally speaking, this is not how you should approach power BI. Instead do the following
- Prepare your data in a star schema
- Load all the data you will need for all reports. You should never have to refresh the report based on different user data needs
- add slicers to the page to apply filters
- forget about writing queries. Instead use the visuals (start with table and matrix) to visually build the output you need. The visual creates the structure/group bys, etc, and measures are replacements for aggregations.