topic Re: Curve color depending on its position relative to another in Report Server

Curve color depending on its position relative to another

Jules_ — Fri, 07 Jul 2023 08:17:29 GMT

I have 2 series of values: val1 and val2, and a column with dates. I want to show a graph with a date x-axis, and the 2 curves corresponding to val1 and val2. The 2nd curve should be red if below curve1 and green if above.

The first solution I came with is to create 2 columns corresponding to val2 in my database: val2_up would have the same value as val2 if above val1, and would be NULL if below val1. Similarly, val2_down would have the same value as val2 if below val1, and would be NULL if above val1.

The problem if I choose this solution is that I cannot add filters on family, category and type. Actually I could, but as PowerBI computes the total, If I choose to show for example only family1, it would compute 2 totals: the first with every values below val1 and the 2nd with every value above val1. Thus it would show 2 curves for val2,

whereas I would only want 1:

If you have any other idea, I would appreciate if you could share it

Re: Curve color depending on its position relative to another

lbendlin — Tue, 11 Jul 2023 00:30:04 GMT

I don't see curves, only straight lines.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Curve color depending on its position relative to another

Jules_ — Tue, 11 Jul 2023 12:08:29 GMT

Correct, I want a line chart, not a curve graph. Sorry for the confusion.

Here is a link to google drive. I uploaded the sample data that I used to get the powerBI visualization above.

https://drive.google.com/file/d/1Wu9sA-RC1LSXBm5Yc6aJ4uNi2XJq21Di/view?usp=sharing

I want these outcomes:

Re: Curve color depending on its position relative to another

lbendlin — Wed, 12 Jul 2023 00:02:14 GMT

If the lines have different slope then they must intersect somewhere in Cartesian Space. That means you can end up in situations where you need to render a red and a green segment.

Anyway, you were close. See attached.

Re: Curve color depending on its position relative to another

Jules_ — Wed, 12 Jul 2023 16:19:01 GMT

Thank you very much!

However it looks like I simplified my problem too much.

Here is what happens when I try this with a bigger sample:

https://drive.google.com/file/d/1Wu9sA-RC1LSXBm5Yc6aJ4uNi2XJq21Di/view?usp=sharing

If one point is below Val1 and the following point is above, there is no line between the two, so I added an orange line behind the green and the red.

Sometimes I don't have value for val1 corresponding to the date of the val2 value, so I tried to change the condition of display: rather than comparing val1 and val2 at each date, I only want to compare them at the last date, when I have both val1 and val2.

Yet I haven't found a good solution, the LASTNONBLANK functions looks bad...

Thanks for your help!

Re: Curve color depending on its position relative to another

lbendlin — Wed, 12 Jul 2023 16:56:46 GMT

You may want to go back into your thinking corner and work on providing sample data that fully covers all your scenarios.

Re: Curve color depending on its position relative to another

Jules_ — Thu, 13 Jul 2023 15:23:17 GMT

Here is the solution I found :

https://drive.google.com/file/d/14Du9Q5tZoDLIdWKZI9F1qL9m5RyI9GuV/view?usp=sharing

I tried to add two columns in my database : val1_last and val2_last, and create 2 measures val2_over and val2_under in powerBI, which are basically the value of val2 if val2_last > val1_last and val2_last < val1_last, else null. With this solution, I would only get the last value of val2 respect the condition.

I entirely filled val1_last and val2_last columns with the last values, even if the date did not correspond to solve the issue.