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


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 
    • v-robertq-msft's avatar
      v-robertq-msft
      Icon for Community Support rankCommunity Support
      5 years ago

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

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