cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Diversity report - Using Measure in Legend of graph

I am trying to build a Diversity report for my organization.

A simple example of the data I work with:

Candidate IDGenderOrg lvl1Org lvl 2Org lvl2ShortlistedInterviewedOffered

 1 Male Sport Squash Ball Yes Yes Yes 2 Female Sport Squash Ball Yes Yes Yes 3 Unknown Sport Squash Ball Yes Yes Yes

I added an excel file with the above and a power PI report here:

I need to show the diversity data for each characteristic Broken down for each recruitment step (shortlisted, Interview, Offer).

As we cannot use measures in the x Axis of a graph

I started by Adding a new column (column1) to use as reference: with each recruitment step.

Using Dax I then created the below measure:

Measure test =
VAR Short = CALCULATE(
SUM( Table1[Candidate Sum] ),
Table1[Shortlisted] ="Yes")
VAR Inte = CALCULATE(
SUM( Table1[Candidate Sum]),
Table1[Interviewed] ="Yes")
VAR Offe = CALCULATE(
SUM (Table1[Candidate Sum]),
Table1[Offered] = "Yes")
RETURN
SWITCH(
SELECTEDVALUE( 'Recrtuiment steps'[Column1]),
"Shortlisting", Short,
"Interview", Inte ,
"Offer", Offe
)

This returns the Diversity data perfectly https://ibb.co/mtdDFtn

Now my issue is that I need to stop the graph from showing the legend data (Gender) when the value is less than 5. The problem is that the legend cannot accept a value.

Adding a if less than 5 works in removing the data:

less than 5 test =
VAR Short = CALCULATE(
SUM( Table1[Candidate Sum] ),
Table1[Shortlisted] ="Yes")
VAR Inte = CALCULATE(
SUM( Table1[Candidate Sum]),
Table1[Interviewed] ="Yes")
VAR Offe = CALCULATE(
SUM (Table1[Candidate Sum]),
Table1[Offered] = "Yes")

RETURN
SWITCH(
SELECTEDVALUE( 'Recrtuiment steps'[Column1]),
"Shortlisting", IF(Short < 5 ,Blank() , Short) ,
"Interview", IF(Inte <5 , Blank() , Inte) ,
"Offer", IF( Offe < 5 , Blank() , Offe )
)
This DAX will remove the data completely if less than 5. Good start but now it looks like we don't have any small numbers instead of hiding them. https://ibb.co/SBYQQ1w

What I need is for the legend to be amended to merge/group categories that are less than into a new "Hidden" value.
So instead of (picturing the graph):
 Non Binary 3% - 3 Unknown 2% - 2 Male 50% - 50 Female 45% -45 shortlist

I currently get:
 Male 50% - 50 Female 47% -45 shortlist

I need:

 Hidden 5% Male 50% - 50 Female 45% -45 shortlist

Is this possible, or am I not using the correct approach?

My set of raw data is bigger but I only selected this to understand the logic. Thank you

Super User

You cannot really create dynamic buckets.  They have to be predefined in a refererence table.  Then you can use the column from that table as the legend, and create your measures around it.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors