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
CliffordAP
Helper II
Helper II

PowerBI - Timeseries of categorical changes

Hello all,

I have a simplified PowerBI data model here:  https://drive.google.com/file/d/13aXUR8mw2MdP3tw9K9LHxhqRCZqPaLwz/view?usp=share_link

I want to make a timeseries visual that accounts for when a user's status category changes over time.

This is what the model looks like: 

CliffordAP_0-1669913922071.png

 

The current status table shows the status of users TODAY, and there is a separate table tracking the historical changes to their status. The visual so far looks like this: 

CliffordAP_1-1669913922072.png

 

I need a measure of some kind that will evaluate the correct count of users' status over time. Something like: 

CliffordAP_2-1669913922073.png

 

I have tried using LOOKUPVALUE, and different forms of CALCULATE but I haven't gotten very far.

Any help is greatly appreciated! Thank you

2 REPLIES 2
brtfrschnr
Frequent Visitor

Test this out with your dataset...

 

This query finds the number of Actives, Frozen, Closed on a given day. I haven't tested if it works when users change from Frozen/Closed back to Active. Here is an output line chart:

 

brtfrschnr_0-1669925818410.png

 

 

let
Source = ***ENTER SOURCE HERE***,

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User ID", type text}, {"Status", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"User ID"]), "User ID", "Status"),
#"Filled Down" = Table.FillDown(#"Pivoted Column",{"1", "2", "3"}),
#"Merged Queries" = Table.NestedJoin(#"Filled Down", {"Date"}, Date, {"Date"}, "Query1", JoinKind.RightOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Date"}, {"Query1.Date"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Query1", each Date.IsInPreviousNMonths([Query1.Date], 2)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Query1.Date", "1", "2", "3"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Query1.Date", "Date"}}),
#"Filled Down2" = Table.FillDown(#"Renamed Columns",{"1", "2", "3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down2", {"Date"}, "Attribute", "Value"),
#"Pivoted Column1" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
#"Sorted Rows" = Table.Sort(#"Pivoted Column1",{{"Date", Order.Ascending}})
in
#"Sorted Rows"

Hello brtfrschnr,
Thank you for your response. I submitted this as a simplified data model with hopes that I can find a methodology with a DAX abstraction. It isn't as feasible for me to restructure the "actual" model in PowerQuery.

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.

Top Solution Authors