Forum Discussion
Microsoft EmployeeHaving both grouped and separated fields in a table
Is there a way to have both items grouped and seperated in a given table?
Example:
| Device |
| Android |
| Windows |
| Mac OS |
| iOS |
| Kingston |
What I need to have:
| Device |
| Android |
| Windows |
| Mac OS |
| iOS |
| Kingston |
| Desktop (new grouped value contains Mac OS and Windows) |
Knowing that I want to use it in a filter:
This can be done by creating a new column as a conditional or custom column after going to data transformation:
Then to union create a new table contains the unioned values from both original device column and the new extracted device:AllDevice = CALCULATETABLE(UNION(VALUES(Table[Device]),VALUES(Table[Device2])))
This new table (AllDevices) can be used as a slicer.
For filteration you may use this:FILTER(Table, OR(Table[Device] IN VALUES(AllDevices[Device]),Table[Device1] IN VALUES(AllDevices[Device]))
10 Replies
- amitchandak
Super User
MohannadSh1996 , actually if create a new column
power query
if [Device] in {"Windows", "Mac OS"} then "Desktop" else [Device]
Dax
If([Device] in {"Windows", "Mac OS"}, "Desktop", [Device])
Then you have drill down kind of structure. But you option to add value means you have create an independent table with all these values and append Desktop to it.
And then based in value change filter values
MohannadSh1996I think that the following dax function will not extract both seperated and grouped:
If([Device] in {"Windows", "Mac OS"}, "Desktop", [Device])
Instead It will have a new table with the following values: Extracted Devices Table = {iOS, Android, Kingston, Desktop}; without Mac and WindowsAlso I have a lot of values other than Windows & Mac OS that I didn't mention in the question which I think it is hard to count.
BTW, suppose that we unioned all the values from these two tables (Device and Extracted Devices Table), how to filter based on this new table to show both Mac and Windows in case of desktop!- amitchandak
MohannadSh1996 , A mesure like
measure =
var _1 = countx(filter(allselected(Device),Device[Device] = "Desktop"), Device[Device])+0
return
if(_1 >0 , calculate(countrows(Table), filter(Table, (Table[device] in values(Device[Device]) || Table[device] in {"Windows", "Mac OS"}))),
calculate(countrows(Table), filter(Table, (Table[device] in values(Device[Device])))))Device table is not joined with table
- mussaenda
Community Champion
Hi MohannadSh1996,
is it okay to to duplicate your data for windows and mac rows?
I am thinking of creating another column where you use as filter like the photo below
- MohannadSh1996
I have a big size data that I don't think it is fine to duplicate it.
But maybe we can extend just the Device values table to include new two items windows, mac os and group them while keeping the original ones.BTW, How to duplicate these specific two values?
- mussaenda
to duplicate the values, you will can create two columns where one is as it is and the other one, you sub with desktop. then unpivot them.
Device Device1 Device2 Android Android Android Windows Windows Desktop Mac OS Mac OS Desktop iOS iOS iOS Kingston Kingston Kingston like this, then unpivot device1 and device2.
hope this helps!
- MohannadSh1996
This can be done by creating a new column as a conditional or custom column after going to data transformation:
Then to union create a new table contains the unioned values from both original device column and the new extracted device:AllDevice = CALCULATETABLE(UNION(VALUES(Table[Device]),VALUES(Table[Device2])))
This new table (AllDevices) can be used as a slicer.
For filteration you may use this:FILTER(Table, OR(Table[Device] IN VALUES(AllDevices[Device]),Table[Device1] IN VALUES(AllDevices[Device]))
