Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
Blog
Twitter
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!
Blog
Twitter
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.