Forum Discussion
Dynamic Group By Table.
- Anonymous3 years ago
Hi JaySharma ,
You can follow the steps below to get it, please check if that is what you want.
1. Create a dimension table using "Enter data" method
2. Create the measures as below
Bins = SWITCH ( SELECTEDVALUE ( 'Bins'[Index] ), 0, "0-" & [NW-low], 1, [NW-low] & "-" & [NW-high], 2, [NW-high] & "-" & 10000000 )Average NW = VAR _len1 = SEARCH ( "-", [Bins], 1, 0 ) VAR _minnw = VALUE ( MID ( [Bins], 1, _len1 - 1 ) ) VAR _maxnw = VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) ) RETURN CALCULATE ( AVERAGE ( 'Table'[Net Worth] ), FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw ) )Count Index = VAR _len1 = SEARCH ( "-", [Bins], 1, 0 ) VAR _minnw = VALUE ( MID ( [Bins], 1, _len1 - 1 ) ) VAR _maxnw = VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) ) RETURN CALCULATE ( COUNT ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw ) )Count Citizen = VAR _len1 = SEARCH ( "-", [Bins], 1, 0 ) VAR _minnw = VALUE ( MID ( [Bins], 1, _len1 - 1 ) ) VAR _maxnw = VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) ) RETURN CALCULATE ( SUM ( 'Table'[Citizen] ), FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw ) )3. Create a table visual which need to include the field [Index] of dimension table 'Bins'
Best Regards
Hi JaySharma ,
I attached the pbix file here, please find the details in it. Please update the max value of parameter 'Slicer 1' and 'Slicer 2' as 10000000 after you got the pbix file. I update their value as 1000 in my attached pbix file due to the community upload file size limitation.
If it is convenient, you can also share your pbix file(exclude sensitive info) with me. Later I can check why the avg and count measures not return the values....
How to upload PBI in Community
Best Regards
Hey Anonymous ,
The initial solution you provided, worked. The issue was with the data model, while trying something out previously, I connected the slicer table with the data table and that's why it wasn't populating. I apologize for the fruitless inconvenience I caused.
Once I removed the useless relations between the tables, everything worked like a charm!
Thanks a ton for helping me out !!!
Regards
Jay Sharma