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 guys!
I need to accomplish a matrix something like that:
My test dataset is:
Id | SubClass_Id | StartDateTime | EndDateTime |
1 | 1 | 13/2/19 7:00 AM | 13/2/19 10:00 AM |
2 | 2 | 26/2/19 12:00 PM | 13/2/19 3:00 PM |
3 | 20 | 18/2/19 6:00 PM | 20/2/19 6:00 PM |
I need to count how many hours are allocated in each day and then display this value in the correct position.
My question is:
How to write the right measure to calculate and display these values?
So far I have accomplished this:
Where the measure is being displayed everywhere and all subclasses appear in every single class.
PS: The class and subclass are properly linked in the Relationship tab.
I would really appreciate any suggestions.
Thanks!
Solved! Go to Solution.
Hi @marcusvmc ,
New a blank query in Query Editor mode, paste below M code into Advanced Editor to generate a calendar table.
let StartDate= #datetime(2019,1,1,0,0,0), EndDate = DateTime.LocalNow(), Source = List.DateTimes(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.DateTimes([Column1], 24, #duration(0, 1, 0, 0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Datetime"}}) in #"Renamed Columns"
Apply above changes. In Data view mode, add below calculated columns in above calendar table.
Year-Month = FORMAT([Datetime],"MMMM YYYY") WeekNum = "Week-"&WEEKNUM([Datetime],2) WeekDay = WEEKDAY([Datetime],2)
Create a calculated table.
crossjoin = ADDCOLUMNS ( FILTER ( CROSSJOIN ( Query1, 'Source Table' ), [Datetime] >= 'Source Table'[StartDateTime] && [Datetime] < 'Source Table'[EndDateTime] ), "Hour", 1 )
Drag corresponding fields from 'CrossJoin' table into Matrix, as shown in below screenshot.
Best regards,
Yuliana Gu
Hi @marcusvmc ,
New a blank query in Query Editor mode, paste below M code into Advanced Editor to generate a calendar table.
let StartDate= #datetime(2019,1,1,0,0,0), EndDate = DateTime.LocalNow(), Source = List.DateTimes(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.DateTimes([Column1], 24, #duration(0, 1, 0, 0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Datetime"}}) in #"Renamed Columns"
Apply above changes. In Data view mode, add below calculated columns in above calendar table.
Year-Month = FORMAT([Datetime],"MMMM YYYY") WeekNum = "Week-"&WEEKNUM([Datetime],2) WeekDay = WEEKDAY([Datetime],2)
Create a calculated table.
crossjoin = ADDCOLUMNS ( FILTER ( CROSSJOIN ( Query1, 'Source Table' ), [Datetime] >= 'Source Table'[StartDateTime] && [Datetime] < 'Source Table'[EndDateTime] ), "Hour", 1 )
Drag corresponding fields from 'CrossJoin' table into Matrix, as shown in below screenshot.
Best regards,
Yuliana Gu
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |