I would like to be able to change the metric displayed on the report based on a paramenter.
A drop down box on the Dashboard has 2 possible selections A and B - only 1 can be selected
If A is selected the formula for the metric is Z+Y
If B is selected the formula for the metric is Z-Y
I believe I would have to start by loading a phony table with just the values of A and B - and then add a filter onto the dashboard for the selection. But after that I am stuck.
Thanks for your help
Best answer by Zuzana BednarovaView original
thanks for the question! I am not able to provide the exact MAQL syntax as it really depends on your logical data model.
But in general you should be able to use IF THEN ELSE logical statement and dashboard filter if the attributes and facts are in the same table.
Let me provide an example on my dummy project with single table in the model.
So let's assume I have table which gives me data about movies, there is year when the movie was released (as attribute which will be used as filter), gross amount, sold tickets per genre and #movies per genre (both facts).
Now the logic I am after is: IF genre year = 2000 then give me GrossGenre metric ELSE give me MoviesPerGenre metric END.
How to write it?
Here is the syntax:
select if GenreYear = 2000 then GrossGenre [Sum] else MoviesPerGenre [Sum] end
Of course the syntax can be more complex, you can add more conditions, use filters, or you can also use CASE syntax.
Please see the documentation: if-then-else , case
Here is my simplified model:
Here is the report with 3 metrics: GrossGenre [Sum] , MoviesPerGenre [Sum] , IF THEN ELSE
As you can see, the result for IF THEN ELSE metric takes correctly the grossgenre metric instead of moviespergenre metric for year = 2000. It can be also used with dashboard filters.
Please give it a go and let me know if you face any issues.
Feel free to contact support via email and invite us to your project so we can review the model to be able to provide exact syntax for your use case.
Thank you for the inspiration. My use case is a bit different.
In your example I want the user to be able to select Gross Genre or Movies Per Genre from the dropdown.
then show the selected measure for all applicable visuals