Forum Discussion
Min/Max Marker in Linechart
Hi Anonymous
For the first question, you can refer change your measure to the following, and my date hierarchy is year-week-date
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
week level
Year level
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.
Hi Anonymous
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).
For the day and Week it works, but for the month, its not working
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:
Hi Anonymous
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.
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 Anonymous
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 resultIf I put customer order and the minmax in a table it shows the following
In comparison, for the weeks it shows only the max as expected
Hi Anonymous
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 resultThe 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 Anonymous
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 resultand with that it shows everything I wished for 🙂
Thank you for your quick and detailed help.
I wish you a nice monday and a good week!