Forum Discussion
Bin dynamically group by sets
- Anonymous5 years ago
Hi hernandezguzman,
I succeed to compress these fields and now you can just use two fields to get correspond values.
bin num = VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1 Bin Names = VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _sizeAs I said, data view level slicer/filter are host ton the child table that generated from data model table which calculated columns/table host so you can't interact them to get dynamic results.
For this scenario, I'd like to suggest use query parameters, you can create two date type query aptamer and they allow you to input values. You can create a blank query table to store the query parameters values.(let's named it as 'filter range' table)Creating Tables In Power BI/Power Query M Code Using #table()
After these stpes, you can create a calculated table to filter raw table records based on the 'filter range' table and add custom fields with calculated column expressions which I pasted above and you will get a table based on query parameter and dynamic bin ranges. (it will changes every time you modify the query parameter and apply changes)
Fitlered = ADDCOLUMNS ( ADDCOLUMNS ( FILTER ( Customer, [DateEntered] >= MIN ( 'Filter range'[Start] ) && [DateEntered] <= MAX ( 'Filter range'[End] ) ), "bin num", VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1 ), "Bin Names", VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _size )Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng, thanks so much, yes I found a video that does exactly What I am looking for, it does it as you say using calculated columns for static version, it is close to what I need, Only thing is I just need 10 bins but this solution add many more basses on the data set,
please see the video, maybe you can see somothing that makes the trick, or can finally make see that this is not possible
thank you in advance I am pretty knew at PBI and have struggled to get this exact as the client resquest, just need what the videos does but always 10 or less bin bars.
thanks so much
Regards
but it makes the chart to increase the number of bins
Hi hernandezguzman,
So you only required the 'bin No' and 'bin range' fields mentioned in the video? If that is the case, can you please share some dummy data and expected results to test? It should help us test and coding formulas.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- hernandezguzman5 years agoRegular Visitor
Hi, thanks so much for answering, I really appreciate it,
yes what I need is exact the same from the video but keeping always the same number of bins,Please download the dummy example I cretated for you to check, it is a simple customer table and I applied the solution to balance the customer in same quantities, please notice that there is a date filter so when you select a week range it shows around 10 or less bins but if you increase the date range selection it increases the number of bins and that what I do not want, I want to keep always 10 or less bins so the only thing that should change is the quantity of customers in bin,
Please download it this link,
https://1drv.ms/u/s!AsQpVn9npEIt0Hwp7hS0dnSm0xx8?e=JhEcQOThanks so much
Regards,- Anonymous5 years agoNot applicable
Hi hernandezguzman,
I succeed to compress these fields and now you can just use two fields to get correspond values.
bin num = VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1 Bin Names = VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _sizeAs I said, data view level slicer/filter are host ton the child table that generated from data model table which calculated columns/table host so you can't interact them to get dynamic results.
For this scenario, I'd like to suggest use query parameters, you can create two date type query aptamer and they allow you to input values. You can create a blank query table to store the query parameters values.(let's named it as 'filter range' table)Creating Tables In Power BI/Power Query M Code Using #table()
After these stpes, you can create a calculated table to filter raw table records based on the 'filter range' table and add custom fields with calculated column expressions which I pasted above and you will get a table based on query parameter and dynamic bin ranges. (it will changes every time you modify the query parameter and apply changes)
Fitlered = ADDCOLUMNS ( ADDCOLUMNS ( FILTER ( Customer, [DateEntered] >= MIN ( 'Filter range'[Start] ) && [DateEntered] <= MAX ( 'Filter range'[End] ) ), "bin num", VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1 ), "Bin Names", VAR _bin = SQRT ( COUNTROWS ( Customer ) - 1 ) - 1 VAR _size = CEILING ( DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ), 1 ) RETURN ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _size )Regards,
Xiaoxin Sheng
- hernandezguzman5 years agoRegular Visitor
Hi Xiaoxin Sheng,
thanks so much, I really appreciate your help it has been so helpful, just last question, when you say, query parameter, is it the same as direct query? I guess so, but want to be sure, I am clear that it cannot be dynamically based on child filters and this is the solution I will implement but just want to be sure about the query parameter = direct query
thanks so much for tanking the time and answer in susch a detail, really appreciate it