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 @toni14
Open edit Queries,
in Table1, select "Total active hours", "Total inactive hours","Total hours logged on" columns and unpivot them.
then merge columns "User" and "Attribute" with separator "space".
The same done in the Table2
Close&&apply
create a relationship between Table1 and Table2 based on merged column
create measures in Table1 and Table2
in Table1
seconds1 = VAR h = SEARCH ( "h", MAX ( Table1[Value] ), 1, 0 ) VAR m1 = LEFT ( MAX ( Table1[Value] ), h - 1 ) VAR m = SEARCH ( "m", MAX ( Table1[Value] ), 1, 0 ) VAR m2 = MID ( MAX ( Table1[Value] ), h + 2, m - ( h + 2 ) ) VAR s = SEARCH ( "s", MAX ( Table1[Value] ), 1, 0 ) VAR m3 = MID ( MAX ( Table1[Value] ), m + 2, s - ( m + 2 ) ) RETURN m1 * 60 * 60 + m2 * 60 + m3
In Table2
seconds2 = VAR h = SEARCH ( "h", MAX ( Table2[Value] ), 1, 0 ) VAR m1 = LEFT ( MAX ( Table2[Value] ), h - 1 ) VAR m = SEARCH ( "m", MAX ( Table2[Value] ), 1, 0 ) VAR m2 = MID ( MAX ( Table2[Value] ), h + 2, m - ( h + 2 ) ) VAR s = SEARCH ( "s", MAX ( Table2[Value] ), 1, 0 ) VAR m3 = MID ( MAX ( Table2[Value] ), m + 2, s - ( m + 2 ) ) RETURN m1 * 60 * 60 + m2 * 60 + m3
Then create measures in Table1
sub = [seconds1]-[seconds2] h_m_s = INT ( [sub] / 3600 ) & "h " & INT ( ( [sub] - INT ( [sub] / 3600 ) * 3600 ) / 60 ) & "m " & MOD ( [sub], 60 ) & "s"
Best reagrds
Maggie
Hi Mr. Juanli,
Thank You very much for your reply, I'll try this. 🙂
I Have last question I hope so. How can I show visual theese two tables in a way that woould be graph options where I can see deviations ?
I have tryed Your solution and I have a little problem. Parameters in column second2 are wrong. Second2 column show me the same value in every row (341.648,00).
Thank You very much for your reply I solved this earlier 🙂
Hi,
I created a graph that shows conditions of eleven month and graph that shows conditions of twelve month. Picture bellow.
I want that data label shows me a value option where I can clearly see how much hours, minutes and seconds for each user.
Hi @toni14
You could turn on the "data label" bar on the visual format pane.
If this doesn't help, please share which columns or measures added in the visual and which data you want to show.
Since your previous problem is sloved, It is better for you to post another to get more helps
Best Regards
Maggie
“ Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“
Hi,
I know i turn on data label but informations that i want isn't show on graph.
To me the most important thing to show up is a column "Value" where I will see everything i need on graph.
Hi @toni14
when i turn on the data label bar, it shows as below
However, it seems you want to show value like "**h**m**s" shown on the visual, right?
i'm afraid it doesn't support to display this currently.
for a workaround, you could add [sub] in a card visual on another page, then make this page as a tooltip, called page3
next, turn on "tooltip" for your visual, select "report page", use the page3
More details
create tooltips based on report pages.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your reply.
I want to show a Value from tables i import on the beginning in the place that shows theese (0,31 M, 0,60M...). I will send you a screenshot again.
Hi @toni14
Just change the column used in the "report tooltip", then you can see values from this column.
Best Regards
Maggie
Hi @toni14
Sorry for not completing my solution, i am working on this problem.
i will come back as soon as i figure out it.
Best Regards
Maggie
Hi @toni14
Open edit Queries,
in Table1, select "Total active hours", "Total inactive hours","Total hours logged on" columns and unpivot them.
then merge columns "User" and "Attribute" with separator "space".
The same done in the Table2
Close&&apply
create a relationship between Table1 and Table2 based on merged column
create measures in Table1 and Table2
in Table1
seconds1 = VAR h = SEARCH ( "h", MAX ( Table1[Value] ), 1, 0 ) VAR m1 = LEFT ( MAX ( Table1[Value] ), h - 1 ) VAR m = SEARCH ( "m", MAX ( Table1[Value] ), 1, 0 ) VAR m2 = MID ( MAX ( Table1[Value] ), h + 2, m - ( h + 2 ) ) VAR s = SEARCH ( "s", MAX ( Table1[Value] ), 1, 0 ) VAR m3 = MID ( MAX ( Table1[Value] ), m + 2, s - ( m + 2 ) ) RETURN m1 * 60 * 60 + m2 * 60 + m3
In Table2
seconds2 = VAR h = SEARCH ( "h", MAX ( Table2[Value] ), 1, 0 ) VAR m1 = LEFT ( MAX ( Table2[Value] ), h - 1 ) VAR m = SEARCH ( "m", MAX ( Table2[Value] ), 1, 0 ) VAR m2 = MID ( MAX ( Table2[Value] ), h + 2, m - ( h + 2 ) ) VAR s = SEARCH ( "s", MAX ( Table2[Value] ), 1, 0 ) VAR m3 = MID ( MAX ( Table2[Value] ), m + 2, s - ( m + 2 ) ) RETURN m1 * 60 * 60 + m2 * 60 + m3
Then create measures in Table1
sub = [seconds1]-[seconds2] h_m_s = INT ( [sub] / 3600 ) & "h " & INT ( ( [sub] - INT ( [sub] / 3600 ) * 3600 ) / 60 ) & "m " & MOD ( [sub], 60 ) & "s"
Best reagrds
Maggie
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |