topic Re: Calculated Column: Return a value only once on the same day for the same resource. in DAX Commands and Tips

Calculated Column: Return a value only once on the same day for the same resource.

Buckeyes2016 — Tue, 01 Aug 2023 00:30:14 GMT

Hello,

I am working to create a column in my PowerBI TimeEntry data table that returns the value 8 for "available hours" only once for a given employee on a given date. My data currently looks like the first three columns on the table below and I would like to create a 4th column that looks like the table below. I have tried several approaches and have not found something that works. Thank you so much for your help!

Resource	Date	Available Hours	Return
Employee 1	1/1/2023	8	8
Employee 1	1/1/2023	8
Employee 1	1/1/2023	8
Employee 1	2/1/2023	8	8
Employee 1	3/1/2023	8	8
Employee 1	3/1/2023	8
Employee 2	2/1/2022	8	8
Employee 2	2/1/2022	8
Employee 2	2/1/2022	8
Employee 2	4/1/2022	8	8
Employee 2	5/1/2022	8	8
Employee 2	5/1/2022	8

Re: Calculated Column: Return a value only once on the same day for the same resource.

Buckeyes2016 — Tue, 01 Aug 2023 00:32:30 GMT

Here is a better view of what I am trying to return. Thanks!

Re: Calculated Column: Return a value only once on the same day for the same resource.

Anonymous — Wed, 02 Aug 2023 03:03:10 GMT

Hi Buckeyes2016 ,

You can follow the steps below to get it, please find the details in the attachment.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VMFTSUTLUN9Q3MjAyBjItlGJ1qCprhFfWmCxZI7wmUy5rglfWlGjZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, Date = _t, #"Available Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}, {"Date", type date}, {"Available Hours", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Resource","Date","Available Hours"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Index"}, {"Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Index", "Return", each if [Index]=1 then [Available Hours] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"

Best Regards