Solved

Use filter as a parameter


Hello,

 

I would like to be able to change the metric displayed on the report based on a paramenter.

For example

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

icon

Best answer by Zuzana Bednarova 6 May 2021, 17:24

Hi Linda,

 

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.

 

Happy reporting!

Zuzana

View original

2 replies

Hi Linda,

 

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.

 

Happy reporting!

Zuzana

Hi Zuzana,

 

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

Reply