cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpf5046
Helper I
Helper I

Need a new DAX formula - Box and Whisker

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:

 

cars dashboard basic.jpg

 

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.

 

unleaded gas slicer.jpg

 

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:

 

only three example before.png

 

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).

 

before ford is selected.png

 

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

 

ford seleced.png

 

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?

 

 

1 ACCEPTED SOLUTION
4 REPLIES 4
OwenAuger
Super User
Super User

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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

@jpf5046

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

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. 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors