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 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] * _size
As 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 Anonymous , could you share the final file ?
Regards,