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,
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")
Solved! Go to 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.
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.
2.) the background should be colored red (transparent), when the value of the running column ist on "f" or "false". Like in this example
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.
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
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.
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |