cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver I

## Dynamic category on slicer section

Hello,

Please help me in creating a creating catogaries based on the mid point selections. For below scatter cart X and Y axis are calculated based on the slicers in left.

If the Midpoint1 = 140 and Midpoint2 = 120, all the dots in chart:

between 0 to 140 in X axis and 0 to 120 in Y axis will be Category A

Above 140 in X Axis and 0 to 120 in Y Axis will be Category B

Above 140 in X Axis and above 120 in Y axis will be Category C

0 to 140 in X Axis and Above 120 in Y axis will be Category D

Expected Output similar to:

2 ACCEPTED SOLUTIONS
Super User

@vin26 , you have to create a measure like this example measure and use that in conditional formatting

``````Color Dot =
var _avgDisc = CALCULATE([Discount %],ALL(Retail))
var _avgMargin = CALCULATE([Margin %],ALL(Retail))
return switch(TRUE(),
[Margin %]>_avgMargin && [Discount %] <_avgDisc , "Green",
[Margin %]>_avgMargin && [Discount %] >_avgDisc , "Blue",
[Margin %]<_avgMargin && [Discount %] <_avgDisc , "Yellow",
[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")``````

I have explained how to do it in this webinar

https://youtu.be/Q1vPWmfI25o?t=2851

Super User

@vin26 - I think it is because of your logical expressions, perhaps:

``````Measure =
var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
return
switch(MAX('Table'[Category]),
"A",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
"B",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
"C",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
"D",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
)``````

If not, maybe try eliminating some CALCULATE statements:

``````Measure =
var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
return
switch(MAX('Table'[Category]),
"A",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
"B",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
"C",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
"D",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Community Support

Hi @vin26 ,

As @Greg_Deckler said ,you could use SWITCH() or use IF()function like this:

``````categoryType =
IF (
[forX] >= 0
&& [forX] <= 140
&& [forY] >= 0
&& [forY] <= 120,
"Categoty A",
IF (
[forX] > 140
&& [forY] >= 0
&& [forY] >= 0
&& [forY] <= 120,
"Category B",
IF (
[forX] > 140
&& [forY] > 120,
"Category C",
IF ( [forX] >= 0 && [forX] <= 140 && [forY] > 120, "Category D" )
)
)
)``````

Best Regards,

Eyelyn Qin

Super User

@vin26 , you have to create a measure like this example measure and use that in conditional formatting

``````Color Dot =
var _avgDisc = CALCULATE([Discount %],ALL(Retail))
var _avgMargin = CALCULATE([Margin %],ALL(Retail))
return switch(TRUE(),
[Margin %]>_avgMargin && [Discount %] <_avgDisc , "Green",
[Margin %]>_avgMargin && [Discount %] >_avgDisc , "Blue",
[Margin %]<_avgMargin && [Discount %] <_avgDisc , "Yellow",
[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")``````

I have explained how to do it in this webinar

https://youtu.be/Q1vPWmfI25o?t=2851

Resolver I

Hi @amitchandak thank you for this solution, it works fine for color changes in chart.

I would also require group the values by category, for that I have created a table with categories and created a measure like below:

``````Measure =
var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
return
switch(MAX('Table'[Category]),
"A",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc),
"B",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc),
"C",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc),
"D",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc)
)``````

it is not working and getting below error:

Super User

@vin26 - I think it is because of your logical expressions, perhaps:

``````Measure =
var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
return
switch(MAX('Table'[Category]),
"A",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
"B",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
"C",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
"D",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
)``````

If not, maybe try eliminating some CALCULATE statements:

``````Measure =
var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
return
switch(MAX('Table'[Category]),
"A",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
"B",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
"C",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
"D",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver I

Hi @Greg_Deckler Perfect!, thanks a ton, both works!

Super User

@vin26 - Seems like you could create a column like:

``````Column =
VAR __MidX = <calculate mid>
VAR __MidY = <calculate mid>
RETURN
SWITCH(TRUE(),
<logical criteria 1>,"Category 1",
<logical criteria 2>,"Category 2",
<logical criteria 3>,"Category 3",
<logical criteria 4>,"Category 4",
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors