Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
Could really use some assistance on this one...
I have a table that shows processes assigned to sales and i need be able to report the number of sales per process state per process on a given date.
Here is an example of my data.
CountSales = --Date User has selected --Unrelated VAR mindt = MIN(DATES[Date]) RETURN COUNTX( --Find process active on date user selects SUMMARIZE( FILTER(SALES_PROCESS,SALES_PROCESS[validfrom] <= mindt && SALES_PROCESS[validTo] >= mindt), SALES_PROCESS[SaleID],SALES_PROCESS[Commission] ), --Count the number of sales SALES_PROCESS[SaleID] )
This is what I'm using but it double counts when there are multiple processes active on the selected date.
When there are multiple processes on the selected date I need to
1) Count the one with the lowest process state number. [See Sale 3 & 5]
2) If the process state numbers are the same I need to count the first one [See Sale 1]
Any help would be very much appreciated.
Solved! Go to Solution.
hi @uk_tj,
I think I have cracked it at least against the sample data set.
created
1 testing measure and 2 filtering measures
//Find rows in date range in range = CALCULATE(COUNTROWS('sample'), FILTER('sample','sample'[validfrom] <= min(DATES[Date]) && 'sample'[validTo] >= MIN('DATES'[Date])))
//set rows to blank() if not in range //Find min datefrom per salesId MinDatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ValidFrom]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())
//set rows to blank() if not in range
//find min ProcessState per salesId minProcessStatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ProcessState]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())
with these mesure created I can count the rows where the date = the min date and the processState = the min ProcessState
Count = CALCULATE(COUNTROWS('sample'), filter('sample', [ValidFrom]=[MinDatePerGroup] && [ProcessState] = [minProcessStatePerGroup]))
If there are two with the same state are the same then the min date wins, if the process states are different but the date is the same then the min process state wins.
link to the pbix below
Proud to be a Super User!
This is where I'm at atm...
Needless to say this is not returning the correct results.
CountSales = --Date User has selected --Unrelated VAR mindt = MIN(DATES[Date]) RETURN COUNTROWS( --Filter summary to show only processes where the index equals the minimum valid index for that sale FILTER( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( --Filter table1 to show ALL valid processes then summarise FILTER(ALL(Table1),Table1[validfrom] <= mindt && Table1[validTo] >= mindt), Table1[SaleID],Table1[Index], Table1[SaleProcess],Table1[ProcessState] ), "MinState", --For each row in summary find the minimum process state for that sale CALCULATE( MIN(Table1[ProcessState]), FILTER(Table1,Table1[SaleID]=[SaleID]&&Table1[validfrom]<=mindt&&Table1[validTo]>=mindt) )), "MinIndex", --For each row in summary find minimum index for that sale CALCULATE( MIN(Table1[Index]), FILTER(Table1,Table1[SaleID]=[SaleID]&&Table1[ProcessState]=[MinState]&&Table1[validfrom]<=mindt&&Table1[validTo]>=mindt) ) ), [Index]=[MinIndex] ) )
haven't given up, made some progess this morning then once again my job got in the way. will look again tonight
Proud to be a Super User!
@richbenmintz thats great as the only progression on mind side is the severity of my headache 🙂
hi @uk_tj,
I think I have cracked it at least against the sample data set.
created
1 testing measure and 2 filtering measures
//Find rows in date range in range = CALCULATE(COUNTROWS('sample'), FILTER('sample','sample'[validfrom] <= min(DATES[Date]) && 'sample'[validTo] >= MIN('DATES'[Date])))
//set rows to blank() if not in range //Find min datefrom per salesId MinDatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ValidFrom]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())
//set rows to blank() if not in range
//find min ProcessState per salesId minProcessStatePerGroup = if([in range] = 1, CALCULATE(min('sample'[ProcessState]), filter(ALLEXCEPT('sample', 'sample'[SaleID]), [in range] = 1)), blank())
with these mesure created I can count the rows where the date = the min date and the processState = the min ProcessState
Count = CALCULATE(COUNTROWS('sample'), filter('sample', [ValidFrom]=[MinDatePerGroup] && [ProcessState] = [minProcessStatePerGroup]))
If there are two with the same state are the same then the min date wins, if the process states are different but the date is the same then the min process state wins.
link to the pbix below
Proud to be a Super User!
Thanks @richbenmintz that did the trick! Was kind of like what I was trying to achieve within a summarise structure but your method is no doubt more efficient with the added bonus of actually working 🙂
Your help is very much appreciated.
Hi @uk_tj,
Honestly I started off with a summerize approach as well, however it became apparent that trying to manage the context and apply blank() to the rows that needed to be hidden was going to make me crazy. went back to the tried and true approach of turning the problem into a series of filtering steps and voila a solution that worked and hopefully will continue to work
Proud to be a Super User!
Bump
sorry, got sucked into work on Friday. I will try to get to it tonight or tomorrow
Proud to be a Super User!
bump
Hi @uk_tj,
You could try using the topn to your function to return a single row per grouping. If you are able to send some sample data or pbix. it would save me from having to transcribe it from your screenshot.
Thanks,
Proud to be a Super User!
Thanks for your reply @richbenmintz, I was beginning to think I smell or something 🙂
Here is a link to My File
Let me know if you have any questions
Hi @uk_tj,
Could you send me the data from your example screenshot, would be easier to work with that.
Sorry.
Proud to be a Super User!
Any ideas? or am I boned?
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |