Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
I need a measure of some kind that will evaluate the correct count of users' status over time. Something like:
I have tried using LOOKUPVALUE, and different forms of CALCULATE but I haven't gotten very far.
Any help is greatly appreciated! Thank you
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:
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.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |