Forum Discussion
Solved
UnPivot DAX
Hello,
I am looking for a project, according to a number of months between 2 dates, resources in a matrix (see illustration)
I thought about doing either doing a benchmark query to build the projection, but maybe there is a DAX solution that would be faster for volume management ?
Do you have any leads?
Thank you
Like this for example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLWN9c3MjAyBDINDfSNDWEcAz0QaaQUqxOt5ARkmembwNUZ6hsbwDhGIDOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Projet = _t, #"Date prévi début" = _t, #"Date prévi fin" = _t, Direction = _t, #"Chef de projet" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date prévi début", type date}, {"Date prévi fin", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Date prévi début],1+Duration.Days([Date prévi fin]-[Date prévi début]),#duration(1,0,0,0))), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date") in #"Expanded Date""Total" is a measure
Total = AVERAGE(Projets[Chef de projet])+AVERAGE(Projets[Direction])
4 Replies
Like this for example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLWN9c3MjAyBDINDfSNDWEcAz0QaaQUqxOt5ARkmembwNUZ6hsbwDhGIDOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Projet = _t, #"Date prévi début" = _t, #"Date prévi fin" = _t, Direction = _t, #"Chef de projet" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date prévi début", type date}, {"Date prévi fin", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Date prévi début],1+Duration.Days([Date prévi fin]-[Date prévi début]),#duration(1,0,0,0))), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date") in #"Expanded Date""Total" is a measure
Total = AVERAGE(Projets[Chef de projet])+AVERAGE(Projets[Direction])many thanks 👌 👏
Not sure what this has to do with time intelligence, but in a matrix visual you can choose to show your measures "on rows" to achieve the desired visual output.
Thank you
but how to go from the 1st table to the 2nd table?
With powerQuery?
