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

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

Reply
5hrch
Regular Visitor

Min/Max Marker in Linechart

Hi

 

I have two questions regarding Min and Max Markers in a linechart. I followed a guide on youtube to add Min/Max Markers in a linechart and id worked correctly. However, I'd like to adjust it to my personal needs, but am struggeling with it.

For the Min/Max Markers I used the following measure
Customer MaxMarker =
Var Customer = [Customer order]
Var MaxCustomer=
MAXX(
ALLSELECTED(dimDate[Year],dimDate[Week]),
[Customer order]
)
Var MinCustomer=
MINX(
ALLSELECTED(dimDate[Year],dimDate[Week]),
[Customer order]
)
Var result =
SWITCH(
TRUE(),
Customer = MaxCustomer="Green",
Customer = MinCustomer=, "Red",
"Black"
)
RETURN result

 

Question 1: In my linechart I would like to highlight the Min and Max Markers depending on the Drilldown State. I'm pretty sure I have to adjust it in the ALLSELECTED line, but I cant figure out how.

5hrch_1-1712839483451.png

 

Question 2:

When I have two lines in my chart from separate measures and then switch to bar chart to set up the conditional formatting for the Min/Max Markers, the field for conditional formatting dissapears

5hrch_2-1712839775219.png

5hrch_3-1712839850343.png

Is there any workaround to highlight the min/max for each of these lines?

 

Thanks in advance for the help

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi @5hrch 

For the first question, you can refer  change your measure to the following, and my date hierarchy is year-week-date 

vxinruzhumsft_0-1712891618493.png

MEASURE =
VAR _maxdata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MAXX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MAXX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            MAXX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
        )
    )
VAR _mindata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MINX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MINX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            MINX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Customer order] = _maxdata, "green",
        [Customer order] = _mindata, "red",
        "black"
    )

Output (I set the data label color)

date level

vxinruzhumsft_1-1712891642265.png

week level

vxinruzhumsft_2-1712891714688.png

Year level

vxinruzhumsft_3-1712891767345.png

 

For the second question, becasue you have two measures in the y-axis, so it has the label, you can not set the chart color by conditionfal formatting, you can only change the chart color manually, so it is better that set the data label color, it can use the conditionfal formatting.

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
v-xinruzhu-msft
Community Support
Community Support

Hi @5hrch 

For the first question, you can refer  change your measure to the following, and my date hierarchy is year-week-date 

vxinruzhumsft_0-1712891618493.png

MEASURE =
VAR _maxdata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MAXX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MAXX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            MAXX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
        )
    )
VAR _mindata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MINX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MINX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            MINX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Customer order] = _maxdata, "green",
        [Customer order] = _mindata, "red",
        "black"
    )

Output (I set the data label color)

date level

vxinruzhumsft_1-1712891642265.png

week level

vxinruzhumsft_2-1712891714688.png

Year level

vxinruzhumsft_3-1712891767345.png

 

For the second question, becasue you have two measures in the y-axis, so it has the label, you can not set the chart color by conditionfal formatting, you can only change the chart color manually, so it is better that set the data label color, it can use the conditionfal formatting.

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hello @v-xinruzhu-msft 

Thanks for your response and help. Its working so far.

Because my date hierarchy is Year , Month, Week, Date I tried to add the month by myself but somehow I'm making something wrong (beginner, sorry :() 
For the max I did it like this:

VAR _maxdata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MAXX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
    	IF (
        	ISINSCOPE ( dimDate[Week] ),
       		MAXX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
        		IF (
            			ISINSCOPE ( dimDate[Monthname] ),
            			MAXX ( ALLSELECTED ( dimDate[Monthname] ), [Customer order] ),
           			MAXX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
        )
    )
)

But its showing green for every month.
I created the Datetable in PowerQuery and I'm having a column MonthNo for the correct sortorder for my Monthname. Is it possible that the error has something to do with that or did I just something wrong in the measure?

Best regards

Hi @5hrch 

Thanks for your quickly reply, if you put the monthname to the scope, you need to make sure that you have put the monthname to the  hierarchy, you can refer to the following picture.

vxinruzhumsft_0-1713144790130.png

Then you can change the code to the following,

 

MEASURE =
VAR _maxdata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MAXX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MAXX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            IF (
                ISINSCOPE ( dimDate[MonthName] ),
                MAXX ( ALLSELECTED ( dimDate[MonthName] ), [Customer Order] ),
                MAXX ( ALLSELECTED ( dimDate[Year] ), [Customer Order] )
            )
        )
    )
VAR _mindata =
    IF (
        ISINSCOPE ( dimDate[Date] ),
        MINX ( ALLSELECTED ( dimDate[Date] ), [Customer order] ),
        IF (
            ISINSCOPE ( dimDate[Week] ),
            MINX ( ALLSELECTED ( dimDate[Week] ), [Customer order] ),
            IF (
                ISINSCOPE ( dimDate[MonthName] ),
                MINX ( ALLSELECTED ( dimDate[MonthName] ), [Customer Order] ),
                MINX ( ALLSELECTED ( dimDate[Year] ), [Customer order] )
            )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Customer order] = _maxdata, "green",
        [Customer order] = _mindata, "red",
        "black"
    )

 

It can work

vxinruzhumsft_1-1713145371510.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

Hi @v-xinruzhu-msft 

I do have the month in the hierarchy and the measure should be correct. I only changed the SWITCH so that it only shows the max  - min and everything else I used rgba(0,0,0,0).

5hrch_0-1713165442938.png

For the day and Week it works, but for the month, its not working

5hrch_1-1713165562205.png
Dont know if the dimDate table is wrong, I created it according to a youtube video I found

let
    // Variabeln
    StartDate = #date(2024, 1, 1),
    EndDate = Date.EndOfYear(Today),
    Duration = Duration.Days(Duration.From(EndDate-StartDate))+1,
    Today = DateTime.Date(DateTime.LocalNow()),

    //Date Columns
    Dates = List.Dates(StartDate,Duration, #duration(1,0,0,0)),
    #"In Tabelle konvertiert" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Datum"}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Datum", type date}}),
    #"Jahr eingefügt" = Table.AddColumn(#"Geänderter Typ", "Jahr", each Date.Year([Datum]), Int64.Type),
    #"Quartal eingefügt" = Table.AddColumn(#"Jahr eingefügt", "QuartalNo", each Date.QuarterOfYear([Datum]), Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Quartal eingefügt", "Quartal", each "Q" & Text.From([QuartalNo])),
    #"Monat eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "MonatNo", each Date.Month([Datum]), Int64.Type),
    #"Name des Monats eingefügt" = Table.AddColumn(#"Monat eingefügt", "Monatsname", each Text.Start(Date.MonthName([Datum]),3), type text),
    #"Wochentag eingefügt" = Table.AddColumn(#"Name des Monats eingefügt", "Tag der Woche", each Date.DayOfWeek([Datum]) + 1),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Wochentag eingefügt",{{"Tag der Woche", Int64.Type}}),
    #"Name des Tags eingefügt" = Table.AddColumn(#"Geänderter Typ1", "Name des Tags", each Date.DayOfWeekName([Datum]), type text),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Name des Tags eingefügt", "KW", each if Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday)+1)+10)/7) = 0
        then 
            Number.RoundDown((Date.DayOfYear(#date(Date.Year([Datum])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Datum])-1,12,31), Day.Monday)+1)+10)/7)
        else if (Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday)+1)+10)/7) = 53 and (Date.DayOfWeek(#date(Date.Year([Datum]),12,31), Day.Monday)+1<4))
        then 
            1
        else 
            Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday)+1)+10)/7)),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte1",{{"KW", Int64.Type}, {"Quartal", type text}})
in
    #"Geänderter Typ2"

and then it looks like this:

5hrch_2-1713166056733.png

 

Hi @5hrch 

Did you also change the code for the min part and in the min part, please make sure in monthname scope, you use the minx() function.

vxinruzhumsft_0-1713167211061.png

If it still not works, can you provide your whole code of the measure?

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @v-xinruzhu-msft 
The measure is:

MinMaxCustomerorder = 
Var Customerorder = [Customer order]
VAR MaxCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MAXX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MAXX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monatsname]),
                MAXX(ALLSELECTED(dimDate[Monatsname]), [Customer order]),
                MAXX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
VAR MinCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MINX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MINX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monatsname]),
                MINX(ALLSELECTED(dimDate[Monatsname]), [Customer order]),
                MINX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
Var result =
SWITCH(
    TRUE(),
    Customerorder = MaxCustomerorder, "Red",
    Customerorder = MinCustomerorder, "rgba(0, 0, 0, 0)",
    "rgba(0, 0, 0, 0)"
)
RETURN result

If I put customer order and the minmax in a table it shows the following

5hrch_0-1713172789152.png

In comparison, for the weeks it shows only the max as expected

5hrch_1-1713172854303.png

 

Hi @5hrch 

Please try the following.

 

MinMaxCustomerorder = 
Var Customerorder = [Customer order]
VAR MaxCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MAXX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MAXX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monat]),
                MAXX(ALLSELECTED(dimDate[Monat]), [Customer order]),
                MAXX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
VAR MinCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MINX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MINX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monat]),
                MINX(ALLSELECTED(dimDate[Monat]), [Customer order]),
                MINX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
Var result =
SWITCH(
    TRUE(),
    Customerorder = MaxCustomerorder, "Red",
    Customerorder = MinCustomerorder, "rgba(0, 0, 0, 0)",
    "rgba(0, 0, 0, 0)"
)
RETURN result

The measure can work well in my sample

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xinruzhu-msft 

Still doesnt work, but I think I found where the problem was and hopefully fixed it.

Because I have a MonthNo column to sort my months corretly I have to add dimDate[MonthNo] also in the allselected statement.

MinMaxCustomerorder = 
Var Customerorder = [Customer order]
VAR MaxCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MAXX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MAXX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monat]),
                MAXX(ALLSELECTED(dimDate[Monat],dimDate[MonthNo]), [Customer order]),
                MAXX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
VAR MinCustomerorder =
    IF (
        ISINSCOPE(dimDate[Datum]),
        MINX(ALLSELECTED(dimDate[Datum]), [Customer order]),
        IF (
            ISINSCOPE(dimDate[KW]),
            MINX(ALLSELECTED(dimDate[KW]), [Customer order]),          
            IF (
                ISINSCOPE(dimDate[Monat]),
                MINX(ALLSELECTED(dimDate[Monat],dimDate[MonthNo])), [Customer order]),
                MINX(ALLSELECTED(dimDate[Jahr]), [Customer order])
            )
        )
    )
Var result =
SWITCH(
    TRUE(),
    Customerorder = MaxCustomerorder, "Red",
    Customerorder = MinCustomerorder, "rgba(0, 0, 0, 0)",
    "rgba(0, 0, 0, 0)"
)
RETURN result

and with that it shows everything I wished for 🙂 

5hrch_5-1713178213129.png

Thank you for your quick and detailed help.


I wish you a nice monday and a good week!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.