Creating a slicer using DAX in PowerView

0
votes

I am working to make a dashboard in MS PowerView but having some trouble creating "slicers" for my dashboard. A slicer is basically a filter for the whole dashboard which filters out the whole display based on what is selected. For example, I am working with a survey so if we click on person type 1 under the "person" slicer, only responses from person type 1 will show.

I am trying to make a slicer based on statistical measure type. Basically, I want a slicer with 3 different statistical measures: average, median, and mode. So when "average" is selected only those graphs using the measure of "average" will display.

So far, all I have is a table in MS Access called "measure name" and I have made this a slicer on my table. However, in order for my slicer to work I believe I have to use a DAX formula. Any idea how to do this? I have tried if(MeasureName[MeasureType] = "Average") but I'm not sure what to put for the true and false.

Any feedback would be appreciated!! :)

2
sql-serverdaxpowerviewpowerbi

2 Answers

0
votes

You can make a slicer that allows users to choose the measure to be shown in charts on the canvas. But as Rory said, you cannot use it to show/hide graphs. It would just change the measure used in the graphs.

Jason Thomas has a good blog post on creating a slicer to choose measures in Power Pivot. It works similarly in a Tabular model.

  1. Create a table of measures with an ID column.

  2. Create a measure based on that table to find the minimum ID value from the slicer selection. This tells you which measure is chosen.

  3. Replace the measures in your charts with this new measure.

I don't consider this to be against Kimball at all. It has less to do with the dimensional model and more to do with the technological solution required for the desired UI capabilities. Basically, you can have your dimensional model and have this table out outside of that without feeling like you are breaking the rules.

-1
votes

A slicer is usually an attribute of one of your dimensions. When you filter on that attribute the filter applies also to your fact table. So if you have a dimension with three entries: average, median and mode it would be possible to create one fact table that stacks the average, median and mode values so that when the slicer is clicked the fact table is filtered for that attribute. That will allow you to show the three types on one graph. It won't allow you to show some graphs and not others. And this type of fact table kind of goes against what Kimball might say about fact table design ...