Percentile of a table stored in a variable
Hi all, I would like to detect outliers in my daily data report by using the percentile function. I have a detail table (table 1) and I want to calculate the percentile of the second table which shows the tickets per day. If I create a fixed calculated table I have to add "ALL / AllExcept" function inside Percentile function: Percentile = PERCENTILEX.EXC (ALL(TicketsByDate), TicketsByDate[Count_Tickets], .90 ) I need to apply external filters and I don't want to rely on the All/AllExcept function. For this reason, I want to store my calculated table in a variable. The problem is that I can't calculate the percentile of this calculated table in a variable. I get the following error: "If k is not a multiple of 1/(n + 1), PERCENTILEX.EXC will interpolate to determine the value at the k-th percentile." Can someone help me? Thanks!!!
Expand Dates in Large Data Set - PowerQuery and Dax
I have a large data set in a proper star schema. However, i'm struggling with some reports out of it. I'll explain what i'm currently doing, and hopefully someone can tell me a better way (or more elegant) way to accomplish something similar. Data set: There's many more tables that have proper relationships (Aprox 30 more), but for the purpose of this exercise, that's all we need. Most of the work happens in the ResourceActualDetail table it looks like this (with some columns deleted): ResourceAllocationId AllocationPercentage EndDate StartDate 794 1 4/1/2018 0:00 2/25/2018 0:00 795 1.15 3/4/2018 0:00 2/25/2018 0:00 795 1.0875 3/11/2018 0:00 3/4/2018 0:00 795 1 4/1/2018 0:00 3/11/2018 0:00 796 1.05 3/4/2018 0:00 2/25/2018 0:00 797 0.725 3/4/2018 0:00 2/25/2018 0:00 1531 1 3/11/2018 0:00 2/25/2018 0:00 1721 1 4/1/2018 0:00 2/25/2018 0:00 Desired Output: ResourceAllocationId AllocationPercentage EndDate StartDate Date 794 1 4/1/2018 0:00 2/25/2018 0:00 2/26/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 3/4/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 … 794 1 4/1/2018 0:00 2/25/2018 0:00 4/1/2018 0:00 Notice the 7 day increments. And the same for every ResourceAllocationID so that I can graph it like so: (error in Graph dates, should be with 7 day increments). Where there is a different line for each Resource (connected through relationships to ResourceAllocation and ResourceActualDetail). Even if we can get it to a point where we can do that for each ResourceAllocationId I can figure out the rest 🙂 Current Approach: These data tables are quite large already(~1M rows) , and my approach only has bloated that. Trying to do everything as close to the source, this what I came up with: Let Source=PowerBI.Dataflows(null), ... #"Changed Type" = Table.TransformColumnTypes(ResourceActualDetail1,{{"EndDate", type number}, {"StartDate", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Numbers([StartDate],([EndDate]-[StartDate])/7,7)), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}, {"EndDate", type datetime}, {"StartDate", type datetime}}) in #"Changed Type1" Essentially, I change the StartDate and EndDate fields to numeric Values and then I calculate the beginning of each week before i switch back all those values to Date type. This part of the query doesn't fold, so I cannot take the advantage of the PowerFlow to do it. It also makes each refresh very slow (should be refresed multiple times a day). Any suggestions or ideas?? Obviously looking for the most efficient way to achieve this so that it won't take many resources (I do have a premium instance). Thanks!
date intelligenceate
Hello guys, i'm doing some manipulations with dates CAn u help with it because i cant find a solution i need. On my page i will have to filter tables : year and month number I want to count how many rows i have that satisfy my condition. Condition is about : if i choose a date ( year and month), how many rows i have for which my date is in interval of date of entry and date of depart. ( two columns of dates that are in my principal data table). IT'is like to be able to choose a date (year and month) and then compare it to my date of entry and date of depart. If a choosen date is otsuide or inside the interval. I decided to start with a simple : i want to calculate how many rows i have for which YEAR of my date of entry is less then the YEAR selected in a filter. Thanks for any help Hope it was clear. I send you some screens to let it be more precise.Date table sorting error
Hi everyone, The extract of my current dates table: DatesTable = VAR MinYear = YEAR ( MIN ( 'Power BI users extract'[Date] ) ) VAR MaxYear = YEAR ( MAX ( 'Power BI users extract'[Date] ) ) RETURN ADDCOLUMNS ( FILTER ( CALENDARAUTO( ), AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )), "Calendar Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "MMM" ), "Month Number", MONTH ( [Date] ), "Weekday", FORMAT ( [Date], "ddd" ), "Weekday number", WEEKDAY( [Date] ), "Period", FORMAT( [Date], "MMM-YYYY" ), "Week number" , WEEKNUM([Date], 2), "Week", "W" & FORMAT( [Date], "WW-MMM-YY" ), "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1) The table below is showing the report views count per week: So i have managed to create a date table with the help of other users and everything was fine untill i tried to sort one of my tables based on the week value. i tried to sort by week both ascending descending and im not getting the correct sorting. The only waz i can get it to work is when i add the week number to my table and the issue with this approach is on the following screenshot. Power bi is putting the last weeks of 2019 after the first weeks of 2020. I would appreciate if anyone has an idea on how to tackle this issue. Thanks in advance
