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 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
- Anonymous5 years agoNot applicable
Hi hernandezguzman,
Since query parameters are hosted on backend query tables, so modify the query parameters not directly change current tables and records.
For this scenario, you need to apply the change and refresh your report to force re-generate the data model tables based on changed parameters
Then calculate table will also change based on the latest version query parameters. (they not dynamic enough as live mode which changes immediately)BTW, direct query mode has limited you to use DAX functions in calculated fields(column/table).
Regards,
Xiaoxin Sheng
- hernandezguzman5 years agoRegular Visitor
Thanks so much Xiaoxin Sheng,
I am now clear about it and your answers helped me a lot, I appreciate all your patience and help,
Regards,