Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a set of data that is divided into distinct groups (Group-A through Group-E). In each group, I have a set of events that occur over a time period of 16 hours. Each event has 64 distinct values that are ordered. Each ID correspondes to a different sensor. Within each group, an ID may appear many times, but in each group, each day and ID combination is unique. For example, in Group A, on 6/1, ID=4813 will only appear once, but ID=4813 appears again on 6/7.
I would like to be able to plot an entire group together, regardless of the date of the event, which I attempted to do by creating an "index" that is repeated, from 0-63. Unfortunately, Power BI sums these values together in one group when the ID is the same. As seen in the screen shots below, even with different dates, when id=4813 and index=0 , Power BI sums the values together and I get one line, instead of two distinct lines. I have also included screen shots of the values to help make it more clear.
How do I get Power BI to treat these events as distinct events, so I can plot each group in one graph?
Solved! Go to Solution.
Hi @Anonymous ,
We can create a calculate column to give every Event a unique name:
Event = VAR t = FILTER ( 'Table', 'Table'[index] = 0 ) VAR t2 = ADDCOLUMNS ( t, "r", RANKX ( t, DATEDIFF ( DATE ( 1970, 1, 1 ), [time], HOUR ) + [id],, ASC, SKIP ) ) VAR i = [id] VAR time = [time] VAR t3 = FILTER ( t2, AND ( [id] = i, DATEDIFF ( time, [time], HOUR ) <= 16 ) ) RETURN "Event " & MAXX ( t3, [r] )
Then we can use this column as the Legend to get multi Lines.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
We can create a calculate column to give every Event a unique name:
Event = VAR t = FILTER ( 'Table', 'Table'[index] = 0 ) VAR t2 = ADDCOLUMNS ( t, "r", RANKX ( t, DATEDIFF ( DATE ( 1970, 1, 1 ), [time], HOUR ) + [id],, ASC, SKIP ) ) VAR i = [id] VAR time = [time] VAR t3 = FILTER ( t2, AND ( [id] = i, DATEDIFF ( time, [time], HOUR ) <= 16 ) ) RETURN "Event " & MAXX ( t3, [r] )
Then we can use this column as the Legend to get multi Lines.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If I understand this correctly, could you not CONCATENATE () the ID and the index, to give you unique identifier for each time?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C wrote:Hi @Anonymous ,
If I understand this correctly, could you not CONCATENATE () the ID and the index, to give you unique identifier for each time?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Hi Nathaniel. Unfortunately, since the ID can appear more than once in each group, I end up with the same situation.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |