Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

A line chart where values are tied to a repetitive index

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?

 

 

this is actually something like 25 events, but only 4 linesthis is actually something like 25 events, but only 4 lines

time series screen shot 1.pngtime series screen shot.png

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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] )

14.PNG

 

Then we can use this column as the Legend to get multi Lines.

 

15.PNG

 

 

 

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.

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.

View solution in original post

3 REPLIES 3
v-lid-msft
Community Support
Community Support

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] )

14.PNG

 

Then we can use this column as the Legend to get multi Lines.

 

15.PNG

 

 

 

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.

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.
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable


@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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.