Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hey all
Been working with PBI a couple of years and can often find similar issues in here that helps me - but this time I'm stuck... So hope some one is able to help
I have three tables
Case-table
Absense-table
Calender
In the case-table I have a measure that counts the days that each case has been open. In the absense-table I have a similar measure that counts the days of absense
There are no relation between the calender-table and the two others
Date | ID | absense | case |
01-01-2023 | 22 | 1 | |
02-01-2023 | 22 | 1 | |
03-01-2023 | 22 | ||
04-01-2023 | 22 | 1 | 1 |
05-01-2023 | 23 | 1 | 1 |
06-01-2023 | 23 | 1 | |
07-01-2023 | 23 | ||
08-01-2023 | 23 | 1 | 1 |
09-01-2023 | 23 | 1 |
For each ID I need to find the period that contains the last 365 days where the case has been open. The period can be longer than one year, as there can be days where the case is not open. At the same time I need the sum of absense-days in the same period, and divide these.
For exampel: The period containing the last 365 case-days are 1/1-22 to 1/3-23 (425 days). In that period there has been 90 absense-days = 90/365... 25%
Any ideas how to solve this with Dax
Thanks
R_
Solved! Go to Solution.
you can modify the query to produce date lists per ID. Then you can use these to intersect with the case dates for each ID.
It was a proof of concept of implementing UNIONX in Power Query.
Please provide sample data that covers your issue or question completely.
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
The case days computation is relatively trivial
Case Days =
var b = ADDCOLUMNS(Calender,"case",if(SELECTEDVALUE('case'[End date])>=[Date] && SELECTEDVALUE('case'[Start date])<=[Date],1,0))
var c = topn(365,filter(b,[case]=1),[Date],DESC)
return sumx(c,[case])
However the Absense days computation is tricky because DAX still does not have a UNIONX operator. (Please vote for the idea). Still thinking.
Thanks - the case days computation works. Keeping the post open and hope someone have an idea to solve the other part 🙂
Does it have to be DAX or would Power Query be acceptable?
Power Query could be an option
The case start date and case end date could also be a part of the absense-table - if that makes it possible solving it in dax?
No, that won't help. Only UNIONX would help.
Here's the Power Query version.
let
Kilde = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00DUw0jUyMDICcoyAHBMYxzGpODWvOFUpVgei0sBQF6gYKgnSZgjiGAM5AYnFxZllEJVgSVMkY4AcQyM8ZkKMAXGMgRxjGAeuMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start date" = _t, #"End date" = _t, Kolonne1 = _t]),
#"Omdøbte kolonner" = Table.RenameColumns(Kilde,{{"Kolonne1", "Type"}}),
#"Ændret type" = Table.TransformColumnTypes(#"Omdøbte kolonner",{{"Start date", type date}, {"End date", type date}}),
#"Added Custom" = Table.AddColumn(#"Ændret type", "Range", (k)=> List.Generate(()=>Number.From(k[Start date]),each _<=Number.From(k[End date]),each _+1)),
#"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
res = List.Difference(Table.SelectRows(#"Expanded Range",each [Type]="Absense")[Range],Table.SelectRows(#"Expanded Range",each [Type]="Passive")[Range]),
#"Converted to Table" = Table.FromList(res, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"
Hmm - maybe I don't get it 😕
The query ends with one single date collum - how can I achieve the result per ID with these dates?
you can modify the query to produce date lists per ID. Then you can use these to intersect with the case dates for each ID.
It was a proof of concept of implementing UNIONX in Power Query.
Aah - okay. I will give it a go
Thanks for all your help Ibendlin
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |