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 community!
I have a pretty simple table that shows me all status changes for each order:
Order Nr | Status | Status Changed Date |
555 | A | 01.01.2021 |
555 | B | 05.01.2021 |
555 | C | 15.01.2021 |
556 | A | 05.01.2021 |
556 | C | 20.01.2021 |
As you can see there is just a record if a status was changed.
Now I would like to display on a bar chart every day of the current year with number of Orders with a Status <> 'C'.
Bow how?
A simple CALCULATE(DISTINCOUNT(Orders[Orders]), Status <> 'C') will not work since there are no records for each day.
How can this be solved using DAX?
Solved! Go to Solution.
Hi @joshua1990 ,
Maybe something like this? 🙂
The secret is to "unfold" / "expand" the dates in between the status changes. So instead of having your table, I would try to change the grain and get something like this one (just for the Order Nr = 555):
With such a table, Power BI / DAX will have it easy to display the Number of Orders per day (just create a measure with a filter on status <> "c" ) I did the transformation in PQ, where I first created an additional date column which is a bit like an "End Date". With a StartDate and EndDate column grouped by OrderNr it is pretty easy to create the rows between them.
Here the code for the advanced editor. Note, my table is called FillRowsBetweenDates.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1VdJRcgRiA0M9IDIyMDJUitWBSTiBJEyxSDgDsSG6hBnMKGwSIB1GBgiJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Status = _t, #"Status Changed Date" = _t]), #"Grouped Rows" = Table.Group(Source, {"Order Nr"}, {{"Grouping", each _, type table [Order Nr=nullable number, Status=nullable text, Status Changed Date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Order Nr", "Status", "Status Changed Date", "Index"}, {"Order Nr", "Status", "Status Changed Date", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "JoinIndex", each [Index] - 1), #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Order Nr", "Index"}, #"Added Custom1", {"Order Nr", "JoinIndex"}, "Added Custom1", JoinKind.LeftOuter), #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Status Changed Date", "Start Date"}}), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Renamed Columns", "Added Custom1", {"Status Changed Date"}, {"Status Changed Date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom1",{"Index", "JoinIndex"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Order Nr", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"Status Changed Date", type date}}), #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom", each if [Status Changed Date] = null then null else { Number.From ( [Start Date] ) ..Number.From ( [Status Changed Date] ) -1 }), #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom2", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}}) in #"Changed Type1"
Let me know if this helps or if you get stuck somewhere 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @joshua1990 ,
Has your problem been solved, if so, you can mark its correct answer as a mark, if not, provide details and we can help you better.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @joshua1990
If Power query is not an option then another way of doing that using DAX only (must have a date table with or without relationship): https://www.dropbox.com/t/e3B2ky5BjspDINnw
Number of Orders =
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUMX (
VALUES ( Orders[Order Nr] ),
VAR ActiveOrdersTable =
CALCULATETABLE (
Orders,
Orders[Status Changed Date] <= CurrentDate
)
VAR StatusCTable =
FILTER ( ActiveOrdersTable, Orders[Status] = "C" )
RETURN
IF (
COUNTROWS ( ActiveOrdersTable ) > 0 && COUNTROWS ( StatusCTable ) = 0,
1
)
),
REMOVEFILTERS ( 'Date' )
)
Hi @joshua1990 ,
Maybe something like this? 🙂
The secret is to "unfold" / "expand" the dates in between the status changes. So instead of having your table, I would try to change the grain and get something like this one (just for the Order Nr = 555):
With such a table, Power BI / DAX will have it easy to display the Number of Orders per day (just create a measure with a filter on status <> "c" ) I did the transformation in PQ, where I first created an additional date column which is a bit like an "End Date". With a StartDate and EndDate column grouped by OrderNr it is pretty easy to create the rows between them.
Here the code for the advanced editor. Note, my table is called FillRowsBetweenDates.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1VdJRcgRiA0M9IDIyMDJUitWBSTiBJEyxSDgDsSG6hBnMKGwSIB1GBgiJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Status = _t, #"Status Changed Date" = _t]), #"Grouped Rows" = Table.Group(Source, {"Order Nr"}, {{"Grouping", each _, type table [Order Nr=nullable number, Status=nullable text, Status Changed Date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Order Nr", "Status", "Status Changed Date", "Index"}, {"Order Nr", "Status", "Status Changed Date", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "JoinIndex", each [Index] - 1), #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Order Nr", "Index"}, #"Added Custom1", {"Order Nr", "JoinIndex"}, "Added Custom1", JoinKind.LeftOuter), #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Status Changed Date", "Start Date"}}), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Renamed Columns", "Added Custom1", {"Status Changed Date"}, {"Status Changed Date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom1",{"Index", "JoinIndex"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Order Nr", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"Status Changed Date", type date}}), #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom", each if [Status Changed Date] = null then null else { Number.From ( [Start Date] ) ..Number.From ( [Status Changed Date] ) -1 }), #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom2", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}}) in #"Changed Type1"
Let me know if this helps or if you get stuck somewhere 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @joshua1990 ,
https://www.dropbox.com/s/jz5pk5lhl4d5tn9/FillRowsBetweenDates.pbix?dl=0
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |