Power BI: Difference between revisions
mNo edit summary |
No edit summary |
||
Line 17: | Line 17: | ||
===CSV/TSV parse column names from row1=== | ===CSV/TSV parse column names from row1=== | ||
= Table.PromoteHeaders(Source, [PromoteAllScalars=true]) | = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) | ||
===Create a Pivot Table=== | |||
report_day = SUMMARIZE( | |||
'log', | |||
'log'[date], | |||
'log'[API], | |||
"CntRequests", COUNTX(FILTER('log', 'log'[ResCode]<>0), 'log'[ResCode]) , | |||
"CntErrors", IF(ISBLANK(COUNTX(FILTER('log', 'log'[ResCode]<>0 && 'log'[ResCode]<>200), 'log'[ResCode])),0, COUNTX(FILTER('log', 'log'[ResCode]<>0 && 'log'[ResCode]<>200), 'log'[ResCode])) | |||
) |
Revision as of 11:55, 19 April 2024
create date table
MyDateTable = CALENDAR(2022-07-01,TODAY()) or MyDateTable = CALENDAR(MINX('myTable', 'myTable'[created (day)]),TODAY())
add column of first day of week
WeekStart = trunc(MyDateTable[MyDate] - WEEKDAY(MyDateTable[Date],2) + 1) Weekday = WEEKDAY(MyDateTable[Date],2)
convert datetime column to date of monday that week
In Data View, add column using this formular
MyWeekStart = trunc(MyDateTable[MyDate] - WEEKDAY(MyDateTable[MyDate],2) + 1) or my_week_sheet_closed = trunc(d_sheet_view[closure_date] - WEEKDAY(d_sheet_view[closure_date].[Date],2) + 1)
CSV/TSV parse column names from row1
= Table.PromoteHeaders(Source, [PromoteAllScalars=true])
Create a Pivot Table
report_day = SUMMARIZE( 'log', 'log'[date], 'log'[API], "CntRequests", COUNTX(FILTER('log', 'log'[ResCode]<>0), 'log'[ResCode]) , "CntErrors", IF(ISBLANK(COUNTX(FILTER('log', 'log'[ResCode]<>0 && 'log'[ResCode]<>200), 'log'[ResCode])),0, COUNTX(FILTER('log', 'log'[ResCode]<>0 && 'log'[ResCode]<>200), 'log'[ResCode])) )