Having both grouped and separated fields in a table

[MohannadSh1996]

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:

[MohannadSh1996 · Accepted solution]

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]))

[amitchandak]

@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

[MohannadSh1996]

I 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

[MohannadSh1996]

Maybe we can do the following:

FILTER(Table1, OR(Table[Device] IN VALUES(AllValuesTable[Device]),ExtractedTable[Device] IN VALUES(AllValuesTable[Device]))

where AllValuesTable is the merged values between origianal and extracted table.

This solution worked for me 

[v-robertq-msft]

Hi, @MohannadSh1996 

Glad to hear that you have solved the problem by yourself, would you like to mark your own reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MohannadSh1996]

I will add the whole solution before marking it as a solution

[mussaenda]

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!