subset
3 Topics
Interpolation on multiple subset of a table
Hello everyone, I’m working on a project where I need to manage a planning on a power bi. My planning (a big table) is composed of Task and sub-task. I’m only working at the Task level to manage my planning, but each modification will also change each sub-task of the modified task. Each sub-task has a quantity per year. The total quantity and the quantity distribution are fixed, whatever the task modification. So, if the duration is identical before and after the modification, we will only shift the quantity. If the duration is different before and after the modification, we will have a weighted interpolation to conserve the quantity and the distribution. I already found how to interpolate with power bi in the Microsoft community : https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712 but it’s at the table level, not subset of the table. I create an example of my situation with quantity before and after the modification at the task level: Task Sub Task Date Interpolating factor_Old Interpolating factor New Quantity_Old Quantity_New T1 ST1 2000 1 0 50 0 T1 ST1 2001 2 0 100 0 T1 ST1 2002 3 1 150 50 T1 ST1 2003 0 2 0 100 T1 ST1 2004 0 3 0 150 T1 ST1 2005 0 0 0 0 T1 ST2 2000 0 0 0 0 T1 ST2 2001 1 0 75 0 T1 ST2 2002 2 1 75 75 T1 ST2 2003 0 2 0 75 T1 ST2 2004 0 0 0 0 T1 ST2 2005 0 0 0 0 T1 ST3 2000 0 0 0 0 T1 ST3 2001 0 0 0 0 T1 ST3 2002 0 0 100 0 T1 ST3 2003 0 0 0 0 T1 ST3 2004 1 1 0 100 T1 ST3 2005 0 0 0 0 T2 ST1 2000 1 0 25 0 T2 ST1 2001 4 0 25 0 T2 ST1 2002 0 1 0 12,5 T2 ST1 2003 0 2 0 12,5 T2 ST1 2004 0 3 0 12,5 T2 ST1 2005 0 4 0 12,5 T2 ST2 2000 1 0 50 0 T2 ST2 2001 4 0 100 0 T2 ST2 2002 0 1 0 25 T2 ST2 2003 0 2 0 33 T2 ST2 2004 0 3 0 42 T2 ST2 2005 0 4 0 50 T2 ST3 2000 1 0 200 0 T2 ST3 2001 4 0 300 0 T2 ST3 2002 0 1 0 100 T2 ST3 2003 0 2 0 117 T2 ST3 2004 0 3 0 133 T2 ST3 2005 0 4 0 150 A first table summarize the initial situation: Task from to Duration Shift Interpolation T1 2000 2002 3 0 no T2 2000 2001 2 0 no And a second table summarize all modifications (T1 is only shifted, T2 is shifted and interpolated): Task from to Duration Shift Interpolation T1 2002 2004 3 +2 no T2 2000 2001 2 0 no So, on my primary table I need to: create “interpolating factor old” and “interpolating factor new” columns create “Quantity new” column based on modifications. My problem is that I have an idea on how to do each part of my workflow, but I don’t know how to apply it for each sub-task and not for all my table. The only solution that I found is to have a formula for each sub-task with a fixed filter (ex : Table[Sub task] = “ST1”). But in my case, I have more than 30 000 sub-tasks. So, this solution is not possible. I have also tried a filter where: FILTER(Table, Table[Sub task] = EARLIER(Table[Sub task])) but it doesn’t worked. I have already search in the community about my problem, but I didn’t find a similar situation. But maybe I’m not using the correct wording. And in fact, maybe I’m not using the more adequate method to do it. So, I need your help to know how to apply formulas for each sub-task without a filter for each sub-task. If you need more information, I can provide them. Thanks,Solved521Views0likes2Comments
Create subset of a table based on filter slicer
Hi everyone, I would like to create a subset of a larger table based on date parameters I would insert via a filter. My main table has the structure as seen here: Customer Attribute A Attribute B Date Since Date Until A Yes qrv 01.11.2021 B No abd 05.09.2023 C No bbb 20.09.2023 A No qrv 01.01.2016 31.10.2021 B No abc 01.03.2019 05.09.2023 C Yes abc 23.04.2019 19.09.2023 D Yes qrv 27.03.2020 09.10.2021 If there is no value in the column Date Until, it means that this entry is still current and the customer is active (unlike customer D, for example) Now, let's say the user would select the date December 11 2021 via the filter. I would now like to have a subset of the table above which would be, given the selected date, be Customer Attribute A Attribute B A Yes qrv B No abc C Yes abc The reason why I would like to have a subset of the table above is that I would like to use the different columns of this subset of the main table to use in other measures. Can anyone help me in finding the right measure to create the subset as described above? Currently, I tried the following measure but I am not quite sure whether or how I can even access the different columns of the variable subsets in other measures Subset = var subsets= FILTER( maintable, maintable[Date Since]>Date(2021,12,11) && maintable[Date Until]<Date(2021,12,11) ) var colum= COUNTROWS( subsets ) RETURN columSolved991Views0likes2Comments
Subsetting Query in DAX
Hi, I've looked around the internet and aren't able to see a comment on what I'm trying to achieve so I'm hoping someone will be able to to tell me if this is possible in a DAX measure. By way of an example, I want to measure how many customers have a monthly subscription for the first 5 months but those same customers don't have a subscription on the 6th month. The table would be transactional with a record of each month a Customer has been subscribed. Customers being a list ID's and subscriptionMonth would be an integer value with "0" representing the first subscription month. Eg. CustomerID, Subscription Start, Subsciption To , subscriptionMonth 12345, 1 Jul 2022, 31 July 2022, 0 12345, 1 Jul 2022, 31 Aug 2022, 1 12345, 1 Jul 2022, 30 Sept 2022, 2 etc... In SQL it would look something like: select count (distinct Customers) from subscriptions where subscriptionMonth <=5 and Customers NOT in ( select distinct Customers, From subscriptions where subscriptionMonth = 6 ) Basically, I'd like the measure to create a list of Customers that are in the 0 to 5 months and then pass that Customer list through to the next level of the measure see if those Customers are in the list for the 6th month. I'd like to do this dynamically in the front end because I want expand this out and parameterise the 0, 5 and 6 values so the end user could interact with a slider or similar and view the continuance rates for Customers in the 0 to 8 or 10 month range etc... Thanks for your help and I hope this makes sense.Solved827Views0likes3Comments