LineDiagram - dynamicle Backgroundcolor depending on the status of the date

[pavo1891]

Hi,

I want to create a graphical report of the production each day.

The reports should use the same line diagram for each topic (density, temp, speed,...).

For each topic I will use the same data format. You can see an example of the data below.

It contains a lot of extra information. Like which tank was active, was the machine running, which batch was active. 

Now I want to visual the data with a line diagram, at the moment I am using Jaspersoft but I am not really happy with it at the moment. When I searched for other programs I found Power BI.

In this forum I found this tutoriel https://community.powerbi.com/t5/Desktop/Line-Chart-With-States-or-Change-color-with-direction-of-tr... which would be really useful the show graphically that the batch was chanced. But I want some more really information in my diagrams

Is it possible to color the background in green, red or other colors depending on the running column?

for example. When running is false the background should be red.

And is it possible to place a line, when the value of the tank column chances?

like here

('N9', 1570073122745, '1773159', '2.29', '3.23', '2.52', '3', '0.00982411', ‘f’, 'Tank 501'),
('N9', 1570073112719, '1773159', '2.29', '3.23', '2.52', '3', '0.009613628', ‘f’, ‘Tank 506’),

Example:

SELECT "Linie" , "Time" , "Batch" , "UGW" , "OGW" , "UWW" , "OWW" , "Value" , "running" , "Tank"
FROM
(VALUES
('N9', 1570073663380, '1773159', '2.29', '3.23', '2.52', '3', '0.010014164', ‘t’, 'Tank 501'),
('N9', 1570073653409, '1773159', '2.29', '3.23', '2.52', '3', '0.01108741', ‘t’, 'Tank 501'),
('N9', 1570073643339, '1773159', '2.29', '3.23', '2.52', '3', '0.010294775', ‘t’, 'Tank 501'),
('N9', 1570073633408, '1773159', '2.29', '3.23', '2.52', '3', '0.0102112545', ‘t’, 'Tank 501'),
('N9', 1570073623395, '1773159', '2.29', '3.23', '2.52', '3', '0.009373087', ‘t’, 'Tank 501'),
('N9', 1570073613357, '1773159', '2.29', '3.23', '2.52', '3', '0.009588751', ‘t’, 'Tank 501'),
('N9', 1570073603373, '1773159', '2.29', '3.23', '2.52', '3', '0.009080471', ‘t’, 'Tank 501'),
('N9', 1570073593378, '1773159', '2.29', '3.23', '2.52', '3', '0.010075902', ‘t’, 'Tank 501'),
('N9', 1570073583374, '1773159', '2.29', '3.23', '2.52', '3', '0.009167831', ‘t’, 'Tank 501'),
('N9', 1570073573337, '1773159', '2.29', '3.23', '2.52', '3', '0.009524242', ‘t’, 'Tank 501'),
('N9', 1570073563367, '1773159', '2.29', '3.23', '2.52', '3', '0.010155809', ‘t’, 'Tank 501'),
('N9', 1570073553391, '1773159', '2.29', '3.23', '2.52', '3', '0.0098379655', ‘t’, 'Tank 501'),
('N9', 1570073543381, '1773159', '2.29', '3.23', '2.52', '3', '0.009396725', ‘t’, 'Tank 501'),
('N9', 1570073533334, '1773159', '2.29', '3.23', '2.52', '3', '0.009596464', ‘t’, 'Tank 501'),
('N9', 1570073523388, '1773159', '2.29', '3.23', '2.52', '3', '0.008815748', ‘t’, 'Tank 501'),
('N9', 1570073513331, '1773159', '2.29', '3.23', '2.52', '3', '0.009574322', ‘t’, 'Tank 501'),
('N9', 1570073503379, '1773159', '2.29', '3.23', '2.52', '3', '0.009054937', ‘t’, 'Tank 501'),
('N9', 1570073493392, '1773159', '2.29', '3.23', '2.52', '3', '0.009497872', ‘t’, 'Tank 501'),
('N9', 1570073483333, '1773159', '2.29', '3.23', '2.52', '3', '0.009253412', ‘t’, 'Tank 501'),
('N9', 1570073473370, '1773159', '2.29', '3.23', '2.52', '3', '0.0094555775', ‘t’, 'Tank 501'),
('N9', 1570073463389, '1773159', '2.29', '3.23', '2.52', '3', '0.009156211', ‘t’, 'Tank 501'),
('N9', 1570073453369, '1773159', '2.29', '3.23', '2.52', '3', '0.009801511', ‘t’, 'Tank 501'),
('N9', 1570073443331, '1773159', '2.29', '3.23', '2.52', '3', '0.009143017', ‘t’, 'Tank 501'),
('N9', 1570073433392, '1773159', '2.29', '3.23', '2.52', '3', '0.009521014', ‘t’, 'Tank 501'),
('N9', 1570073423394, '1773159', '2.29', '3.23', '2.52', '3', '0.009277264', ‘t’, 'Tank 501'),
('N9', 1570073413348, '1773159', '2.29', '3.23', '2.52', '3', '0.009325625', ‘t’, 'Tank 501'),
('N9', 1570073403382, '1773159', '2.29', '3.23', '2.52', '3', '0.009267662', ‘t’, 'Tank 501'),
('N9', 1570073393353, '1773159', '2.29', '3.23', '2.52', '3', '0.009643512', ‘t’, 'Tank 501'),
('N9', 1570073383331, '1773159', '2.29', '3.23', '2.52', '3', '0.009713668', ‘t’, 'Tank 501'),
('N9', 1570073373347, '1773159', '2.29', '3.23', '2.52', '3', '0.010041937', ‘t’, 'Tank 501'),
('N9', 1570073363345, '1773159', '2.29', '3.23', '2.52', '3', '0.00976648', ‘t’, 'Tank 501'),
('N9', 1570073353407, '1773159', '2.29', '3.23', '2.52', '3', '0.008928707', ‘t’, 'Tank 501'),
('N9', 1570073343332, '1773159', '2.29', '3.23', '2.52', '3', '0.0097916005', ‘t’, 'Tank 501'),
('N9', 1570073333351, '1773159', '2.29', '3.23', '2.52', '3', '0.009046699', ‘t’, 'Tank 501'),
('N9', 1570073323400, '1773159', '2.29', '3.23', '2.52', '3', '0.009471781', ‘t’, 'Tank 501'),
('N9', 1570073313363, '1773159', '2.29', '3.23', '2.52', '3', '0.010091892', ‘t’, 'Tank 501'),
('N9', 1570073303332, '1773159', '2.29', '3.23', '2.52', '3', '0.009359911', ‘t’, 'Tank 501'),
('N9', 1570073293390, '1773159', '2.29', '3.23', '2.52', '3', '0.009146119', ‘t’, 'Tank 501'),
('N9', 1570073283379, '1773159', '2.29', '3.23', '2.52', '3', '0.0101001', ‘t’, 'Tank 501'),
('N9', 1570073273386, '1773159', '2.29', '3.23', '2.52', '3', '0.009704509', ‘f’, 'Tank 501'),
('N9', 1570073263385, '1773159', '2.29', '3.23', '2.52', '3', '0.010061362', ‘f’, 'Tank 501'),
('N9', 1570073253374, '1773159', '2.29', '3.23', '2.52', '3', '0.009918289', ‘f’, 'Tank 501'),
('N9', 1570073243377, '1773159', '2.29', '3.23', '2.52', '3', '0.0093864575', ‘t’, 'Tank 501'),
('N9', 1570073233371, '1773159', '2.29', '3.23', '2.52', '3', '0.009252995', ‘t’, 'Tank 501'),
('N9', 1570073223795, '1773159', '2.29', '3.23', '2.52', '3', '0.009946746', ‘t’, 'Tank 501'),
('N9', 1570073213705, '1773159', '2.29', '3.23', '2.52', '3', '0.009800578', ‘t’, 'Tank 501'),
('N9', 1570073203805, '1773159', '2.29', '3.23', '2.52', '3', '0.009708527', ‘t’, 'Tank 501'),
('N9', 1570073193736, '1773159', '2.29', '3.23', '2.52', '3', '0.010150223', ‘t’, 'Tank 501'),
('N9', 1570073183718, '1773159', '2.29', '3.23', '2.52', '3', '0.009670781', ‘t’, 'Tank 501'),
('N9', 1570073173780, '1773159', '2.29', '3.23', '2.52', '3', '0.010460078', ‘t’, 'Tank 501'),
('N9', 1570073163844, '1773159', '2.29', '3.23', '2.52', '3', '0.010437833', ‘t’, 'Tank 501'),
('N9', 1570073153768, '1773159', '2.29', '3.23', '2.52', '3', '0.009624884', ‘t’, 'Tank 501'),
('N9', 1570073142720, '1773159', '2.29', '3.23', '2.52', '3', '0.009435874', ‘t’, 'Tank 501'),
('N9', 1570073132762, '1773159', '2.29', '3.23', '2.52', '3', '0.009673263', ‘f’, 'Tank 501'),
('N9', 1570073122745, '1773159', '2.29', '3.23', '2.52', '3', '0.00982411', ‘f’, 'Tank 501'),
('N9', 1570073112719, '1773159', '2.29', '3.23', '2.52', '3', '0.009613628', ‘f’, ‘Tank 506’),
('N9', 1570073102724, '1773159', '2.29', '3.23', '2.52', '3', '0.010499933', ‘t’, ‘Tank 506’),
('N9', 1570073092732, '1773159', '2.29', '3.23', '2.52', '3', '0.009493014', ‘t’, ‘Tank 506’),
('N9', 1570073082753, '1773159', '2.29', '3.23', '2.52', '3', '0.009818808', ‘t’, ‘Tank 506’),
('N9', 1570073072739, '1773159', '2.29', '3.23', '2.52', '3', '0.009291929', ‘t’, ‘Tank 506’),
('N9', 1570073062706, '1773159', '2.29', '3.23', '2.52', '3', '0.010230131', ‘t’, ‘Tank 506’),
('N9', 1570073052795, '1773159', '2.29', '3.23', '2.52', '3', '0.009706182', ‘t’, ‘Tank 506’),
('N9', 1570073042739, '1773159', '2.29', '3.23', '2.52', '3', '0.0105181625', ‘t’, ‘Tank 506’),
('N9', 1570073032702, '1773159', '2.29', '3.23', '2.52', '3', '0.009649184', ‘t’, ‘Tank 506’),
('N9', 1570073022744, '1773159', '2.29', '3.23', '2.52', '3', '0.010090913', ‘t’, ‘Tank 506’),
('N9', 1570073012763, '1773159', '2.29', '3.23', '2.52', '3', '0.010234838', ‘t’, ‘Tank 506’),
('N9', 1570073002729, '1773159', '2.29', '3.23', '2.52', '3', '0.009107575', ‘t’, ‘Tank 506’),
('N9', 1570072992705, '1773159', '2.29', '3.23', '2.52', '3', '0.009808798', ‘t’, ‘Tank 506’),
('N9', 1570072982744, '1773159', '2.29', '3.23', '2.52', '3', '0.00984595', ‘t’, ‘Tank 506’),
('N9', 1570072972723, '1773159', '2.29', '3.23', '2.52', '3', '0.01018486', ‘t’, ‘Tank 506’),
('N9', 1570072962762, '1773159', '2.29', '3.23', '2.52', '3', '0.009568854', ‘f’, ‘Tank 506’),
('N9', 1570072952743, '1773159', '2.29', '3.23', '2.52', '3', '0.009695439', ‘f’, ‘Tank 506’),
('N9', 1570072942726, '1773159', '2.29', '3.23', '2.52', '3', '0.009775358', ‘f’, ‘Tank 506’),
('N9', 1570072932742, '1773159', '2.29', '3.23', '2.52', '3', '0.0096220365', ‘t’, ‘Tank 506’),
('N9', 1570072922718, '1773159', '2.29', '3.23', '2.52', '3', '0.010649934', ‘t’, ‘Tank 506’),
('N9', 1570072912726, '1773159', '2.29', '3.23', '2.52', '3', '0.010523076', ‘t’, ‘Tank 506’),
('N9', 1570072902790, '1773159', '2.29', '3.23', '2.52', '3', '0.009912132', ‘t’, ‘Tank 506’),
('N9', 1570072892776, '1773159', '2.29', '3.23', '2.52', '3', '0.010498867', ‘t’, ‘Tank 506’),
('N9', 1570072882788, '1773159', '2.29', '3.23', '2.52', '3', '0.010353998', ‘t’, ‘Tank 506’),
('N9', 1570072872787, '1773159', '2.29', '3.23', '2.52', '3', '0.010207', ‘t’, ‘Tank 506’),
('N9', 1570072862589, '1773159', '2.29', '3.23', '2.52', '3', '0.009555575', ‘t’, ‘Tank 506’),
('N9', 1570072852586, '1773159', '2.29', '3.23', '2.52', '3', '0.009302176', ‘t’, ‘Tank 506’),
('N9', 1570072842615, '1773159', '2.29', '3.23', '2.52', '3', '0.009354256', ‘t’, ‘Tank 506’),
('N9', 1570072832608, '1773159', '2.29', '3.23', '2.52', '3', '0.008745996', ‘t’, ‘Tank 506’),
('N9', 1570072822619, '1773159', '2.29', '3.23', '2.52', '3', '0.010577819', ‘t’, ‘Tank 506’),
('N9', 1570072812583, '1773159', '2.29', '3.23', '2.52', '3', '0.010176298', ‘t’, ‘Tank 506’),
('N9', 1570072802541, '1773159', '2.29', '3.23', '2.52', '3', '0.009938684', ‘t’, ‘Tank 506’),
('N9', 1570072792520, '1773159', '2.29', '3.23', '2.52', '3', '0.009786239', ‘t’, ‘Tank 506’),
('N9', 1570072782523, '1773159', '2.29', '3.23', '2.52', '3', '0.009783035', ‘t’, ‘Tank 506’),
('N9', 1570072772518, '1773159', '2.29', '3.23', '2.52', '3', '0.01032632', ‘t’, ‘Tank 506’),
('N9', 1570072762516, '1773159', '2.29', '3.23', '2.52', '3', '0.009838094', ‘t’, ‘Tank 506’),
('N9', 1570072752540, '1773159', '2.29', '3.23', '2.52', '3', '0.009841015', ‘t’, ‘Tank 506’),
('N9', 1570072742509, '1773159', '2.29', '3.23', '2.52', '3', '0.00977553', ‘f’, ‘Tank 506’),
('N9', 1570072732579, '1773159', '2.29', '3.23', '2.52', '3', '0.010494757', ‘f’, ‘Tank 506’),
('N9', 1570072722521, '1773159', '2.29', '3.23', '2.52', '3', '0.010359799', ‘f’, ‘Tank 506’),
('N9', 1570072712515, '1773159', '2.29', '3.23', '2.52', '3', '0.010155075', ‘f’, ‘Tank 506’),
('N9', 1570072702530, '1773159', '2.29', '3.23', '2.52', '3', '0.009667748', ‘f’, ‘Tank 506’),
('N9', 1570072692522, '1773159', '2.29', '3.23', '2.52', '3', '0.009787411', ‘f’, ‘Tank 506’),
('N9', 1570072682629, '1773159', '2.29', '3.23', '2.52', '3', '0.009443779', ‘f’, ‘Tank 506’),
('N9', 1570072672492, '1773159', '2.29', '3.23', '2.52', '3', '0.010623293', ‘f’, ‘Tank 506’)
) s("Linie" , "Time" , "Batch" , "UGW" , "OGW" , "UWW" , "OWW" , "Value" , "running" , "Tank")

[Icey]

Hi @pavo1891 ,

Attached my PBIX file. What do you want?

Best Regards,

Icey

[pavo1891]

Mornging @Icey ,

thx for the file. Looks greate with the time on the x-axis. My test example showed every time value on the x-axis.

so to the points of this topic.

1.) First I want a line in the Background, when the value of the tank column chances. In this example the value chances from 506 ->501. This can happen from 0 to 5 times during a production.

tank change

2.) the background should be colored red (transparent), when the value of the running column ist on "f" or "false". Like in this example

[Icey · Accepted solution]

Hi @pavo1891 ,

Based on my test, Power BI doesn't support what you want currently. You can create a new idea here to improve Power BI.

One workaround, create some measures like below and change the Type of X axis to "Categorical".

Measure 1 = IF(MAX('Table'[running])="t",MAX('Table'[Value]))

Measure 2 = IF(MAX('Table'[running])="f",MAX('Table'[Value]))

Measure 3 =
VAR NextIndex =
    MAX ( 'Table'[Index] ) + 1
VAR NextTank =
    CALCULATE (
        MAX ( 'Table'[Tank] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = NextIndex )
    )
RETURN
    IF (
        NextTank <> MAX ( 'Table'[Tank] )
            && MAX ( 'Table'[Index] ) <> MAXX ( ALL ( 'Table' ), 'Table'[Index] ),
        MAX ( 'Table'[Value] )
    )

Best Regards,

Icey

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

[pavo1891]

HI @Icey 

this wasn't the solution I was looking for, but this is realy NICE

big thx for the help

[Icey]

Hi @pavo1891 ,

I get your data like below and create a custom column:

Is there any error? And how do you create your line visual? I can't reproduce it.

Best Regards,

Icey

[pavo1891]

Hello icey,

thanks for the reply.

I never worked with PowerBi, I worked with JasperSoft so far.

Maybe it helps, when i tell you how I did it in Jaspersoft.

in Jasper I created a TimeSeriesSpline HTML5 diagram. In the diagram used the time column for the x axis, which was transformed to the datetime format automaticly. For the y axis i used the value column.

I also used the OGW, UGW, OWW and UWW, but they are now not realy necessary.