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

[MoeData]

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

[OwenAuger · Accepted solution]

Hi @MoeData 

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

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

@MoeData 

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:

1. Create measures Salary Filtered and EducationYear Filtered which return blank if there are too few data points.
2. Place these measures on X & Y axes
3. 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

[MoeData]

@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? 

[OwenAuger · Accepted solution]

@MoeData 

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:

1. Create measures Salary Filtered and EducationYear Filtered which return blank if there are too few data points.
2. Place these measures on X & Y axes
3. 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

[MoeData]

@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?   

[OwenAuger · Accepted solution]

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:

1. Create a Report Page tooltip instead which can be customized however you like.
2. 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).

   
3. 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 🙂

[MoeData]

Thanks! You are a genius! 😄 

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

Hi @MoeData 

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

[MoeData]

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!