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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_newuser
Post Prodigy
Post Prodigy

how to set dynamic target line

Hi,

How can I set the target line dynamically?

For example, when I select Product A, the target line and data points color follow target 70%

while for Product B, the target line and data points color follow target 80%.

 

Thanks.

 

color = 
switch ( true(),
rate > 0.7,"green",
"Red"
)

 

PBI_newuser_1-1597732235340.png

 

1 ACCEPTED SOLUTION

HI @PBI_newuser,

Your formula seems already summarized rates, so it get the wrong result(overly 0.7) when I try to aggregated them.
You can try to use the following measures to confirm if it meets your requirement:

Line Color =
VAR currProduct =
    SELECTEDVALUE ( 'Table'[Product] )
RETURN
    IF (
        currProduct = "A",
        IF ( [Rate] > 0.8, "Green", "Red" ),
        IF ( [Rate] > 0.7, "Green", "Red" )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

11 REPLIES 11
amitchandak
Super User
Super User

@PBI_newuser , You can have line like

Line Value =
switch ( true(),
selectedvalue(Table[Product]) ="A", 0.7,
selectedvalue(Table[Product]) ="B", 0.8
)

 

Color I doubt you can have.

Made this on a bar visual and then change to the line.  But because there more then one measure on visual I doubt you can color

 

You have the option of conditional formatting using "field" for a measure

 

Line Value =
switch ( true(),
selectedvalue(Table[Product]) ="A", "Green",
selectedvalue(Table[Product]) ="B", "Red"
)

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

Hi @amitchandak ,

Thanks for your reply.

 

But "Constant Line" can't insert function.

If I insert "Line Value" in Values column, then I can't make it dashed line and also hide the data label only for "Line Value" field.

 

How can I insert the measure only Product A, the target is 0.7, others are 0.8?

Line Value = 
switch ( true(),
selectedvalue(Table[Product]) ="A", 0.7,
selectedvalue(Table[Product]) ="B", 0.8
)

 For color, I need to set if data points more than 0.7 for Product A is green while below 0.7 is red. But, when other products are selected, green for above 0.8 while red for below 0.8. How can I do that?

Line Value = 
switch ( true(),
selectedvalue(Table[Product]) ="A", "Green",
selectedvalue(Table[Product]) ="B", "Red"
)

Thanks.

 

HI @PBI_newuser,

You can add if statement in your conditional format measures expression to setting target based on product.

Line Color=
VAR currProduct =
    SELECTEDVALUE ( Table[Product] )
VAR currValue =
    CALCULATE ( SUM ( Table[Value),allselected(Table),values(Table[Product] ) )
RETURN
    IF (
        _currProduct = "A",
        IF ( currValue > 0.7, "Green", "Red" ),
        IF ( currValue > 0.8, "Green", "Red" )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft , thanks for your reply.

 

I tried with the below measure but all the data points show Green.

Can you help to see what's the problem with measure below?

 

Thanks.

 

Line Color = 
VAR currProduct =
SELECTEDVALUE ( TableB[Product] )
VAR currValue =
CALCULATE (SUMX(TableA, TableA[Rate]),ALLSELECTED(TableB),values(TableB[Product] ) )
RETURN
IF (
currProduct = "A",
IF ( currValue > 0.8, "Green", "Red" ),
IF ( currValue > 0.7, "Green", "Red" )
)

Hi @PBI_newuser,

I'd like to suggest extract the SELECTEDVALUE ( TableB[Product] ) into a new formula to check if it can get current values correctly.
In addition, can you please share some dummy data to test? It should help to trouble and test on measure formulas.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft ,

I am unable to share pbix file here.

Hence, I will just share the data in table below.

 

Below is the Column created:

Period = TableB[Date].[Year] & "-" & FORMAT(TableB[Date].[Date],"MM")
Below is Measures created:
Passed = Sum(TableB[Count])
Total = Count (TableB[Count])
Rate = IFERROR([Passed]/[Total],0)
DateCase NumberProductCount
13-08-19 15:00CN-01084626 1
12-09-19 22:00CN-01046298A0
14-09-19 19:19CN-01047910A0
17-09-19 21:26CN-01061919A0
03-10-19 0:00CN-01065379A0
02-10-19 23:30CN-01065606A0
21-10-19 17:09CN-01067180A0
24-10-19 19:25CN-01067182A0
01-11-19 15:40CN-01068207A0
21-11-19 15:30CN-01068360A0
21-11-19 17:30CN-01068817A1
03-10-19 16:30CN-01065209B0
02-10-19 19:30CN-01066605B0
03-10-19 19:00CN-01068236B1
02-10-19 21:30CN-01068653B1
03-10-19 18:00CN-01068710B0
03-10-19 16:00CN-01068711B0
03-10-19 18:00CN-01068744B1
04-10-19 20:00CN-01068747B1
09-09-19 22:30CN-01074272C0
06-09-19 20:00CN-01084895C1
07-09-19 0:00CN-01087782C0
09-09-19 21:00CN-01088179C0
09-09-19 13:30CN-01112091C1
10-09-19 18:00CN-01112163C1
06-01-20 19:00CN-01101438C0
15-01-20 7:00CN-01114026C1
07-01-20 22:00CN-01118900C1
08-01-20 17:00CN-01120659C1
08-01-20 18:00CN-01127818C0
13-09-19 1:30CN-01070767D1
11-09-19 18:20CN-01072158D0
10-09-19 15:00CN-01072285D1
10-09-19 15:00CN-01075260D0
09-09-19 20:30CN-01075356D1
10-09-19 20:00CN-01075425D1
10-09-19 16:00CN-01075561D1
10-09-19 20:30CN-01077787D1
10-09-19 17:20CN-01079007D1
09-09-19 21:00CN-01081676D1
09-09-19 21:00CN-01082568D0
10-09-19 17:00CN-01084007D1
10-09-19 21:00CN-01084628D0
11-09-19 14:00CN-01085063D1

HI @PBI_newuser,

Your formula seems already summarized rates, so it get the wrong result(overly 0.7) when I try to aggregated them.
You can try to use the following measures to confirm if it meets your requirement:

Line Color =
VAR currProduct =
    SELECTEDVALUE ( 'Table'[Product] )
RETURN
    IF (
        currProduct = "A",
        IF ( [Rate] > 0.8, "Green", "Red" ),
        IF ( [Rate] > 0.7, "Green", "Red" )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft ,

Thanks for your reply. It works now.

How about the target line (Constant Line)?

Is it possible to move the target line to 80% if Product = A is selected?

PBI_newuser_0-1598430909497.png

 

HI @PBI_newuser,

Unfortunately, constant line not same as other aggregate analytic lines, it not able to configure based on measure value. So it is impossible to make a dynamic constant line based on filter selections.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Okay, thanks for your help @v-shex-msft !

pranit828
Community Champion
Community Champion

HI @PBI_newuser 

 

You can use he same measure as a line in Line and stacked column chart.





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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