topic Re: How to set a threshold on number of visible data points in visuals? in DAX Commands and Tips

How to set a threshold on number of visible data points in visuals?

Anonymous — Fri, 18 Mar 2022 10:53:41 GMT

I have a scatter plot visual and several filter scliers to control what shoud be shown on th visual.
I have been looking around to find a way to force the visual to show the data, only if there are more than 2 entities (or row of data) available after filtering, and show nothing otherwise. How can such a threshold be set for a visual? I tried to make a measure with a condition on the number of data points using COUNT, but doesn't do anything.

Link to the sample BPIX file:
https://tinyurl.com/2p8nb4mc

Re: How to set a threshold on number of visible data points in visuals?

OwenAuger — Fri, 18 Mar 2022 13:27:01 GMT

Hi Anonymous

One method is to create a measure that returns the "overall" row count of the DimSalary table using ALLSELECTED, then apply this as a visual level filter.

In the attached PBIX, I created this measure and added it as a visual level filter "greater than 2".

Salary Row Count Overall = CALCULATE ( COUNTROWS ( DimSalary ), ALLSELECTED () )

Does this behave has you expected?

You could solve this with measure(s) as well, but this seems a good enough solution.

Regards,

Owen

Re: How to set a threshold on number of visible data points in visuals?

Anonymous — Mon, 21 Mar 2022 08:17:19 GMT

Hi OwenAuger
Nice! I wasn't using

ALLSELECTED ()

That actually does what I like to get in the end, but how can I have that threshold inside the DAX instead of "Filters" pane? Tried to use FILTER command but I am doing something wrong.

Thanks!

Re: How to set a threshold on number of visible data points in visuals?

OwenAuger — Mon, 21 Mar 2022 11:01:15 GMT

No worries 🙂

To capture the logic in a measure instead, I would write something like this, and place it in the Size field well, keeping the existing X & Y Axis fields:

Salary Count if above threshold = VAR Threshold = 2 VAR SalaryRowCountOverall = CALCULATE ( COUNTROWS ( DimSalary ), ALLSELECTED () ) RETURN IF ( SalaryRowCountOverall > Threshold, COUNTROWS ( DimSalary ) )

This measure will give you larger dots if multiple employees have the same salary/year combination (such as Year 2000 Salary 1,500 in the sample data).

You could force the dots to the same size if you wanted:

Salary Indicator if above threshold = -- Return 1 if "ALLSELECTED" count of DimSalary rows > threshold -- and at least one salary exists in current filter context -- Otherwise BLANK VAR Threshold = 2 VAR SalaryRowCountOverall = CALCULATE ( COUNTROWS ( DimSalary ), ALLSELECTED () ) RETURN IF ( SalaryRowCountOverall > Threshold, VAR SalaryRows = COUNTROWS ( DimSalary ) RETURN DIVIDE ( SalaryRows, SalaryRows ) -- See https://www.sqlbi.com/articles/how-to-return-blank-instead-of-zero/ )

PBIX attached.

Regards,

Owen

Re: How to set a threshold on number of visible data points in visuals?

Anonymous — Tue, 22 Mar 2022 08:38:08 GMT

OwenAuger

Wow, thanks! That also actually works. I like to keep the size constant.
There is just one issue though: It is not possible to add the trendlines under the Analytics tab anymore. Is there a way to add trendlines to the scatter plot you made?

Re: How to set a threshold on number of visible data points in visuals?

OwenAuger — Wed, 23 Mar 2022 00:02:42 GMT

Anonymous

You're welcome 🙂

Sure, to get the trend line working, it appears you can't have a Size field on the visual.

We can take a different approach instead:

Create measures Salary Filtered and EducationYear Filtered which return blank if there are too few data points.
Place these measures on X & Y axes
Place DimEmployees1[EmployeeID] as the Values field.

To define the above measures, I also created a helper measure Is Salary Count above threshold.

So the measures are:

Is Salary Count above threshold = VAR Threshold = 2 VAR SalaryRowCountOverall = CALCULATE ( COUNTROWS ( DimSalary ), ALLSELECTED () ) RETURN SalaryRowCountOverall > Threshold

EducationYear Filtered = IF ( [Is Salary Count above threshold], AVERAGE ( DimEmployees1[EducationYear] ) -- Could use SELECTEDVALUE if assume single value )

Salary Filtered = IF ( [Is Salary Count above threshold], AVERAGE ( DimSalary[Salary] ) -- Could use SELECTEDVALUE if assume single value )

In reality, it is sufficient to apply filtering logic to just one of the X or Y axis measures, but we can apply it to both for completeness.

Updated PBIX attached.

Regards,

Owen

Re: How to set a threshold on number of visible data points in visuals?

Anonymous — Wed, 23 Mar 2022 07:25:21 GMT

OwenAuger
Amazing! This also works well.
One last thing: Is there a way to hide the Employee ID from the tooltip, other than turning the whole tooltip off?

Re: How to set a threshold on number of visible data points in visuals?

OwenAuger — Wed, 23 Mar 2022 20:36:15 GMT

That's great 🙂
The reason for placing EmployeeID in "Values" was to ensure we had one data point per EmployeeID.

Some options I can think of to get ride of EmployeeID, depending on exactly how you want things to look:

Create a Report Page tooltip instead which can be customized however you like.
Instead of placing EmployeeID in "Values", place EducationYear and Salary instead, then click the "expand all down one level" button on the visual.

This results in one data point per EducationYear/Salary combination.

However, the tooltips will appear like this, which might not be desirable (fields can be renamed though).
Create some other arbitrary ID that has a 1:1 relationship with EmployeeID and use it instead.

One thing to note is that you can get a slightly different trend line if you have one data point per Salary/EducationYear combination compared with one data point per EmployeeID (due to duplicates).

Updated PBIX attached.

Regards,

Owen 🙂

Re: How to set a threshold on number of visible data points in visuals?

Anonymous — Wed, 23 Mar 2022 21:39:57 GMT

Thanks! You are a genius! 😄