How to write a DAX which should calculate as per parameter passed?

[Anmolgan]

I want to write a DAX which should calculate some conditions according to the value passed by user in the parameter, what I am trying to say is I want to calculate closing and opening balance for which dates are passed by the user in the parameter and after when it runs every calculation for the dax should be on the behalf of that parameter is this possible? Can anyone point me in the correct direction?

[JarroVGIT]

If my understanding of your problem is correct, then this is definitely possible.

You need to make sure your parameters are loaded into the datamodel zo you can reference them into your DAX expressions. By default, parameters are not loaded into your datamodel.
To do this, right click on the parameter in the query editor and select "Enable Load".

Now, if you reference the parameter in your DAX expressions, you can use the value given by your users.

If this answers your question, please mark it as solution 🙂

[Anmolgan]

Also whenever I try your method to load the parameter directly into the model, I lost the option to change the parameter in PowerBI Service itself, earlier I was using a blank query to take the values of the parameter whenever I change and I was using that table inside the DAX formulas and I was getting the option to change the parameter to any give values in the powerbi service, can you explain what I did wrong here so that i can get back to parameter change option in powerbi service?

[JarroVGIT]

Then I really don't understand what you are trying to achieve. You already had a working workaround for my initial understanding of your problem. Isn't the model reloaded every time you change your parameters, and thus also your dax expressions reevaluated?

[Anmolgan]

I am looking for a way to not refresh the entire query because it takes 30-40 mins to run and re extract all the data from SAP BW enviroment to PowerBI, Am looking for a way to change the parameters and get the desired output in PowerBI Service itself, is that possible?

[JarroVGIT]

Hm I see what you are getting at. Unfortunately I am unaware of a way to reload parts of a dataset in PowerBI Service, and the parameters are part of the dataset once uploaded to the Service. If you change the parameter (online in the Service) you need to refresh the dataset in order for the change in parameters to take effect. 

One possible solution (and i am unsure if that works with SAP BW as well) is incremental refresh. You will need to have Power BI Premium capacity available to you though. This might drastically decrease the amount of time it takes to refresh the dataset once you've changed the parameters. I have little experience with this though, so more on the subject i wouldn't be able to tell you. If you don't have PowerBI Premium available to you, then using parameters are maybe not the right solution to your need? Perhaps creating measures and allowing your consumers to change said measures?

[Anmolgan]

Seems like a probable solution, but the end user will never be able to use the powerbi desktop that freely like a user do using the PowerBI Portal.

[Anmolgan]

@JarroVGIT 

Thanks for you response, I think you understand my problem correctly, and solution is also correct as that is something that I am looking for but my scenerio is somewhat different here. 

I am using the SAP BW connector in order to connect to On-Premise SAP BW system, now the problem is everytime I change my parameter I need to refresh the data in order for my query to take that parameter and calculate everything from scratch, what I am looking for is, a way so that I dont have to refresh my dataset each time I change my parameter, since Query refreshing makes refreshing of the powerbi query and powerbi needs to refetch the data everytime I refresh the query and it takes 30minutes or so to make the refresh and give me the new parameter, How can I dynamically change the parameter so that each time I change the parameter powerbi do not need to refresh the whole query and it should only refresh the parameter to reflect the updated value, in the end I am only making relatioship of my data parameter into the SAP-BW query so that everytime the parameter changes it should take that current date into the query and display the values with all the formulas.

Is that something that is possible?