How to use measure as a slicer

[nainiu440]

Hello,

I have a question about how to use a measure as a slicer. A sample data is like this:

Where each project has its ProjectName, ProjectType, District, and Time Elapsed (how long it takes to finish). Time Elapsed is a measure. In order to find outliers, I calculated the Z-scores using the formula: (Time Elapsed-mean)/standard deviation. And Z-scores is also a measure. Then, I created another measure to indicate if it is an outlier based on the value of Z-scores, if it between -3 and 3, it is not an outlier, otherwise, it is. So far in the visual table, all measures showed the correct results. The next step I need is to be able to filter outliers and non-outliers using a slicer, which I am stuck. I watched the video of banding and segregating, and created a supporting table as below:

and use the following code to calculate Outliers:

Outliers = CALCULATE(
   SELECTEDVALUE('Outlier supporting'[groups],BLANK()),
   FILTER(ALL('Outlier supporting'),
   [zscore]>'Outlier supporting'[Min] &&
   [zscore]<='Outlier supporting'[Max]))

I used the group column as the value of a slicer, but it doesn’t work, it always showed all data no matter what I selected. I am wondering where I did wrong and how to fix this.

In addition, I also tried to use the Filters function, but when I click the Outliers tab, no options is showed up for some reasons.

Thanks!

[Nick_M]

@nainiu440

I have a question about how to use a measure as a slicer

This cannot be done. Filters ( including slicers) have to come from existing tables, which are only updated at data refresh and not when measures are called.

[nainiu440]

There are several methods that could work around to achieve measure slicer if you search online. I watched this video from Sam

And tried to replicate the same technique. Basically, the measure is linked to a supporting table, and used the ‘groups’ column from the supporting table as the slicer.

[Nick_M]

@nainiu440

And tried to replicate the same technique. Basically, the measure is linked to a supporting table, and used the ‘groups’ column from the supporting table as the slicer.

I see now. Yes, what the video shows is definitely correct since that is a physical table. Is there any chance you upload some sample data along with a quick mockup of what your expected outcome would be?

[nainiu440]

Please find a sample file here:
20190830.pbix (339.1 KB)

What I am looking for is to be able to use the colunm ‘groups’ as a slicer to select outliers, or non-outliers showed in the visual table. Currently as you can see below, this slicer did not work (True was selected but still showed data with non-outliers.

I also noticed the filter of groups did not show the correct values. There are three data points with ‘False’, but in filter, it showed one False, and two True points.

thanks!

[Nick_M]

@nainiu440,
I’m taking a look and before I start, is it necessary for these to be installed to see your the issue you are having here?

[nainiu440]

Hi,

I don’t think it is necessary since I did not plot anything, just a visual table. thanks!

[Nick_M]

@nainiu440
I figured, but wanted to make sure. I’ve been looking at this a for a bit and to be honest this is a tough one. I’m still not 100% sure this is possible, but I really feel like it should be. I’m going to keep working on it and will revert back when I have something. I do apologize for the delay

-Nick

[nainiu440]

thanks Nick. I just think the method I referred could be a great way to achieve measure filtering. but if it is too much of a hustle, is that possible to have two virtual tables, one for outliers and one for non-outliers? I uploaded my full data set. If you open it, you can see there are several card values (mean, 75 percentile, and counts). Originally, I’d like to have a filter to select outliers and non-outliers, so that the values of these card could change accordingly. But if I can get two tables, one for outliers and one for non-outliers, and then calculate these values based two tables, will that be something more feasible? thanks!

[Nick_M]

@nainiu440
Thanks for the file. I will take a deeper look. One of the issues is that it looks like the average of what is selected is used in the calculations that lead to what is and is not an outlier. Unless I am mistaken on that part? If something is or is not an outlier then we can just store that as a calculated column and then we can filter that column.

I’ve been thinking of ways to “Send” the true/false of the outlier column to the table to be filtered, but still no such luck. My initial thought was to create virtual table using ADDColumns and then filter that, but getting stuck

But if we can store the outlier True/False into a calculated column it makes this process a whole lot easier.

[nainiu440]

You are correct, outliers are based on what is selected and will change dynamically based on the filter selections. That’s why I used a measure instead of a calculated column. I am also watching several tutorials about harnessing virtual tables as a middle step, and then add filter/or do calculations based on that. I will keep posting if have any progress. thanks!

[Nick_M]

I really feel like there’s a way to do this! I’ll keep working on it too, because now it will just bother me till there’s a solution

[Steve]

Hi @nainiu440 and @Nick_M,
I’m not sure if I understand the question completely but, filtering a measure using a slicer is possible. I’ve taken the liberty of downloading your sample file and re-working the calculations to demonstrate how it would work with your data (refer to Page 2). The filtering of the measure is created by this function.

Count of Tasks with Outlier Filter = 
Var Dist_Table = 
ADDCOLUMNS(
    SUMMARIZE(demo,
    demo[TaskName]),
    "Test", [Outlier Test]
)
return
SUMX(
    FILTER(Dist_Table,
    CONTAINSROW( VALUES('Outlier supporting'[groups]), [Test])),
    1
)

I also simplified the Outlier measure to this.

Outlier Test = SWITCH(TRUE(),

And([New Z Score]>-1,[New Z Score]<1),“False”,

“True”)

and when you want to filter a measure you would apply it like this.

Task Start with Outlier Filter =

CALCULATE([Task Start],
FILTER(
VALUES(demo[TaskName]),
NOT ISBLANK([Count of Tasks with Outlier Filter])
)
)

I’m not sure that this completely answers the question because I don’t understand the relationship between “Districts” and “Projects” with the “Task”. Let me know if you have any questions. 20190830.pbix (358.0 KB)

[Nick_M]

@Steve
That is awesome! I’ll let @nainiu440 confirm if this meets the requirements or not. but even if it doesnt it’s still great work. I will definitely file this one away as I honestly was having a few issues trying to solve this. But that’s the great thing about this community. It’s impossible to know it all, but get a good group of people together and bound to find a solution.

[nainiu440]

Hi @Steve and @Nick_M, thanks for your efforts, and I think Steve’s method is getting me there. However, I cant get the key filtering measure working correctly. When I add this measure, it gives me error of ‘can’t display the visual’.

I understand that we created a middle table ‘Dist_table’, and using SUMX to mark False with 1 and True with blank. But could you please explain this part?

@Steve

SUMX( FILTER(Dist_Table, CONTAINSROW( VALUES(‘Outlier supporting’[groups]), [Test])), 1 )

I thought VALUES(‘Outlier supporting’[groups]) should be {“True”, “False”}, so does [Test], so what did the filter function actually for? I tried to replace this to SUMX( FILTER(Dist_Table, CONTAINSROW( {“True”}, [Test])), 1 ), the display error is gone, but the whole column is blank. I think if I can get this measure working, the rest will be easy. thanks again!

[Steve]

Hi @nainiu440 and @Nick_M, please find attached the updated file. The correction required was to convert the “groups” field in the Outlier supporting groups table to a text field. The reason you do this is true or false need to be a label, not a Boolean test. data draft-part 1_test.pbix (1.6 MB)

However, I since found that was an issue with the calculation when Task Name is included in the level of detail. The duplicate tab contains the amended version. the screenshot below explains the difference in approach.

I’ve also made amendments to the [mean], [Std] and [Actual_Duration_Zscore] where [mean] and [std] now measure the mean and std deviation over all projects and the Z score measure is now in 2 versions one being for the task and the other for the project. The project Z score is used as the value to filter the projectname slicer. Both versions of the Z score are in the table as a reference.

You had a question about the Countrows filter? It sets up a condition which allows you to filter the virtual table using the disconnected table (‘Outlier supporting’[Group]) values as input. It’s similar to the DAX pattern of;

Filter( Dist_Table, [Test] In Values(‘Outlier supporting’[Group])

Let me know what you think.

[nainiu440]

So the mean, std, zscores, and outliers should be calculated dynamically based on what data set is selected using slicers on the top. That’s why I used ALLSELECTED() to calculate mean and std.

I did change the column type to text, and the visual error is gone. However, currently it gave me 1 for both ‘true’ and ‘false’. So when use this filter:

@Steve

Filter( Dist_Table, [Test] In Values(‘Outlier supporting’[Group])

It actually does not filter anything because [Test] include ‘True’ and ‘False’, and so does Values(‘Outlier supporting’[Group]), and that’s why all data have a value of 1. Did I understand this wrong?
thanks!

[Steve]

Hi @nainiu440,
The filter allows you to select Projects that are True, False or both (all).

The problem with the model is the use of the Allselected(Table) measures. The test returns the the status of each project based upon the selected project and tasks (which is what you want). However, when you filter the projects to display only say those that are “True”, the [std] and [mean] measures also change to reflect the new filter condition which in turn changes the Ztest measure.

[nainiu440]

Hi @Steve, I am still a little confused, so through the filter projects with true, false, or both were selected, and then use SUMX to give all of them a value of 1?

You are right about using Allselected(), and there is indeed a such conflict. If I add something like ALL(groups) to the mean and std formulas, excluding groups filter from calculating results, does it sound feasible? Thanks!

[Steve]

Hi @nainiu440,
With respect to the filter, don’t consider True or False as Boolean conditions, they are only labels. You can achieve the same filtering behaviour with different labels. You could change “True” to “Outlier” and “False” to “In Spec” in the test measure. You would need to change the disconnected table labels to “Outlier” and “In Spec” as well but, when you choose “In Spec” from the slicer only the “In Spec” (AKA “False”)are displayed. When you choose “Outlier” only the “True” is displayed and when the slicer has no selections, both “Outlier” and “In Spec” are displayed.

I’ve given some thought to the potential solution and yes, what you’ve indicated is one solution. However, it won’t be as elegant as you would like it to be. The problem is the change in the context created by Allselected() functions when the table is further filtered further from the project level. I’m thinking a pattern like this may do the trick for the [mean] and [std] measures;

Task Mean = CALCULATE([Mean],
Filter(ALL(‘Master_Table’),
Master_Table[TaskName] = Max(Master_Table[TaskName]))
)

This will fix the task mean and std deviation to the tasks selected and should not change when you filter down to individual projects and tasks.

Attached is a Power BI model I created from your data as an example of how it could work. You will notice I’ve changed the structure a bit… I strongly suggest the use of Power Query to shape your data into Lookup and data tables, it improves performance and compression. Let me know what you think.
Project Model.pbix (224.9 KB)