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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vin26
Resolver I
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

 

Sample_pbi1.JPG

 

Expected Output similar to:

Sample_pbi2.JPG

 

Please help me with the logic. 

Sample file: https://www.dropbox.com/s/hemibrwsquy0mhu/Catogary_Sample_PBI.pbix?dl=0 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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

View solution in original post

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
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" )
        )
    )
)

9.4.3.1.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

amitchandak
Super User
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

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

 

PBI2.JPG

 

 

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:

pbi_error.JPG 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Greg_Deckler
Super User
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",
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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