Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
)
Solved! Go to 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
@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
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
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")
Passed = Sum(TableB[Count])
Total = Count (TableB[Count])
Rate = IFERROR([Passed]/[Total],0)
Date | Case Number | Product | Count |
13-08-19 15:00 | CN-01084626 | 1 | |
12-09-19 22:00 | CN-01046298 | A | 0 |
14-09-19 19:19 | CN-01047910 | A | 0 |
17-09-19 21:26 | CN-01061919 | A | 0 |
03-10-19 0:00 | CN-01065379 | A | 0 |
02-10-19 23:30 | CN-01065606 | A | 0 |
21-10-19 17:09 | CN-01067180 | A | 0 |
24-10-19 19:25 | CN-01067182 | A | 0 |
01-11-19 15:40 | CN-01068207 | A | 0 |
21-11-19 15:30 | CN-01068360 | A | 0 |
21-11-19 17:30 | CN-01068817 | A | 1 |
03-10-19 16:30 | CN-01065209 | B | 0 |
02-10-19 19:30 | CN-01066605 | B | 0 |
03-10-19 19:00 | CN-01068236 | B | 1 |
02-10-19 21:30 | CN-01068653 | B | 1 |
03-10-19 18:00 | CN-01068710 | B | 0 |
03-10-19 16:00 | CN-01068711 | B | 0 |
03-10-19 18:00 | CN-01068744 | B | 1 |
04-10-19 20:00 | CN-01068747 | B | 1 |
09-09-19 22:30 | CN-01074272 | C | 0 |
06-09-19 20:00 | CN-01084895 | C | 1 |
07-09-19 0:00 | CN-01087782 | C | 0 |
09-09-19 21:00 | CN-01088179 | C | 0 |
09-09-19 13:30 | CN-01112091 | C | 1 |
10-09-19 18:00 | CN-01112163 | C | 1 |
06-01-20 19:00 | CN-01101438 | C | 0 |
15-01-20 7:00 | CN-01114026 | C | 1 |
07-01-20 22:00 | CN-01118900 | C | 1 |
08-01-20 17:00 | CN-01120659 | C | 1 |
08-01-20 18:00 | CN-01127818 | C | 0 |
13-09-19 1:30 | CN-01070767 | D | 1 |
11-09-19 18:20 | CN-01072158 | D | 0 |
10-09-19 15:00 | CN-01072285 | D | 1 |
10-09-19 15:00 | CN-01075260 | D | 0 |
09-09-19 20:30 | CN-01075356 | D | 1 |
10-09-19 20:00 | CN-01075425 | D | 1 |
10-09-19 16:00 | CN-01075561 | D | 1 |
10-09-19 20:30 | CN-01077787 | D | 1 |
10-09-19 17:20 | CN-01079007 | D | 1 |
09-09-19 21:00 | CN-01081676 | D | 1 |
09-09-19 21:00 | CN-01082568 | D | 0 |
10-09-19 17:00 | CN-01084007 | D | 1 |
10-09-19 21:00 | CN-01084628 | D | 0 |
11-09-19 14:00 | CN-01085063 | D | 1 |
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
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?
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
HI @PBI_newuser
You can use he same measure as a line in Line and stacked column chart.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |