Forum Discussion

MohannadSh1996's avatar
MohannadSh1996
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Having 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 ...
Need Help
Tips and Tricks
  • MohannadSh1996's avatar
    MohannadSh1996
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    MohannadSh1996
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        MohannadSh1996
        Icon for Microsoft Employee rankMicrosoft Employee
        5 years ago

        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