Need a new DAX formula - Box and Whisker

[jpf5046]

Hello – I’m running into a grouping problem with a DAX formula. I will walk through the dashboard and then state the problem.

The dashboard is a collection of races by three different cars, Ford, Audi, and BMW.

The cars have had 12 races on two types of courses (City or Track) and the cars had two gas options (leaded or unleaded).

This is what the dashboard looks like with no slicers selected:

On the right hand side we see the count of races by car type, the Box and Whisker in the middle is showing race outcome.  

---

So for example, when I selected ‘unleaded’ for gas we see Ford does not have any 1st place finishes with unleaded gas and normally finishes 3rd when it does have unleaded gas.

And we also see on the right hand side, Audi and Ford have performed in six races with unleaded gas, and BMW five races.

-----

Starting to get into the problem:

I only want a car type to show in the Box and Whisker when that car type has had at least three races.

Here is an example:

In this example, the Box and Whisker graph is working exactly as I expect because BMW has less than three races and they do not show up on the Box and Whisker plot.

The Box and Whisker is running off the following formula: 

Show when 3 total races = if(CALCULATE(DISTINCTCOUNT(cars[Races]), ALLEXCEPT(cars, cars[Car Type], cars[Gas Type], cars[Race Day], cars[Track or City Course])) > 2.5, sum(cars[Place]), blank())

------

HERE IS AN EXAMPLE OF THE ISSUE

Consider the following, there are four races for each car type (before).

Yet when I state ‘Ford’ in the slicer, I get the following (after):

Even though there are four races by Ford, the Box and Whisker does not show. I expect it to show, because I know Ford has been in four races, even the table on the right has four listed. The only thing that has changed between the last two pictures is I have Ford selected as a slicer. 

I want to show any combination of the four slicers to show in the Box and Whisker plot if the count of races is 3 or above.

Does anyone have any insight on this issue?

[OwenAuger]

Hi @jpf5046

If I understand the situation, you want your measure to show values only for Car Types with at least 3 Races (in the filter context).

In this situation, I would write a measure like:

Show when 3 total races =
CALCULATE (
    SUM ( cars[Place] ),
    FILTER (
        VALUES ( cars[Car Type] ),
        CALCULATE ( DISTINCTCOUNT ( cars[Races] ) ) >= 3
    )
)

I haven't tried to debug the ALLEXCEPT version, but I think this FILTER-based formula should do the trick.

If not, please post back.

Cheers,

Owen 🙂

[jpf5046 · Accepted solution]

@OwenAuger 

Also posted on SO, this solution appears to be working:

http://stackoverflow.com/questions/41602300/only-show-3-or-more-races-a-grouping-issue-with-dax/4160...

[OwenAuger]

@jpf5046

Thanks - I answered without knowing how context worked in the box and whisker visual. That clears it up 🙂

[jpf5046]

Not working just yet. I changed the formula a little becasue I did not have a column names 'Races'. 

Here is what I changed it to: 

At Least 3 Races = CALCULATE (
    SUM ( cars[Place] ),
    FILTER (
        VALUES ( cars[Car Type] ),
        CALCULATE ( DISTINCTCOUNT ( cars[RacesId] ) ) >= 3
    )
)

(changed 'Races' to 'RacesId')

Reading it makes sense, not sure why it does not work in the visual. good thought though.