How to arrange the data in PowerBi using Dax?

Question

I have the data in following format where column 1 is username and column 2 is platforms. I want to create a venn diagram in Power Bi. For that I need to

Format in which I have the data:

User Name	Platforms
User 1	Instagram
User 3	Facebook
User 1	Instagram
User 3	Facebook
User 2	YouTube
User 4	Facebook
User 1	YouTube
User 3	YouTube
User 1	YouTube
User 3	YouTube
User 1	Instagram
User 3	Facebook
User 1	Twitter
User 3	Twitter
User 1	Twitter
User 3	Twitter
User 1	Instagram
User 3	Facebook

Format in which I want to arrange the data:

Required Format

Any help would be appretiated.

I can do this in excel but the problem is that this is a part of larger dataset and hence need to do this programatically through DAX.

Answer 1

Try the following as a Calculated Table: This one is more unique users:

SocialVennDiagram = 
  var gp1Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Instagram")
  var gp2Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Twitter")
  var gp3Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Facebook")
  var gp4Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "YouTube")

  RETURN SELECTCOLUMNS(
    {
      ( 1, 0, 0, 0, COALESCE(COUNTROWS( EXCEPT(gp1Users, UNION(gp2Users, gp3Users, gp4Users)) ), 0) ),
      ( 1, 1, 0, 0, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp1Users, gp2Users), UNION(gp3Users, gp4Users)) ), 0) ),
      ( 1, 1, 1, 0, COALESCE(COUNTROWS( EXCEPT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp3Users), gp4Users) ), 0) ),
      ( 1, 1, 1, 1, COALESCE(COUNTROWS( INTERSECT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp3Users), gp4Users) ), 0) ),
      ( 0, 1, 0, 0, COALESCE(COUNTROWS( EXCEPT(gp2Users, UNION(gp3Users, gp4Users, gp1Users)) ), 0) ),
      ( 0, 1, 1, 0, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp2Users, gp3Users), UNION(gp4Users, gp1Users)) ), 0) ),
      ( 0, 1, 1, 1, COALESCE(COUNTROWS( EXCEPT(INTERSECT(INTERSECT(gp2Users, gp3Users), gp4Users), gp1Users) ), 0) ),
      ( 0, 0, 1, 0, COALESCE(COUNTROWS( EXCEPT(gp3Users, UNION(gp4Users, gp1Users, gp2Users)) ), 0) ),
      ( 0, 0, 1, 1, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp3Users, gp4Users), UNION(gp1Users, gp2Users)) ), 0) ),
      ( 0, 0, 0, 1, COALESCE(COUNTROWS( EXCEPT(gp4Users, UNION(gp1Users, gp2Users, gp3Users)) ), 0) ),
      ( 1, 0, 1, 0, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp1Users, gp3Users), UNION(gp2Users, gp4Users)) ), 0) ),
      ( 1, 0, 1, 1, COALESCE(COUNTROWS( EXCEPT(INTERSECT(INTERSECT(gp1Users, gp3Users), gp4Users), gp2Users) ), 0) ),
      ( 1, 0, 0, 1, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp1Users, gp4Users), UNION(gp2Users, gp3Users)) ), 0) ),
      ( 1, 0, 1, 0, COALESCE(COUNTROWS( EXCEPT(INTERSECT(gp1Users, gp3Users), UNION(gp2Users, gp4Users)) ), 0) ),
      ( 1, 1, 0, 1, COALESCE(COUNTROWS( EXCEPT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp4Users), gp3Users) ), 0) )
    },
    "Number of users", [Value5],
    "Instagram", [Value1],
    "Twitter", [Value2],
    "Facebook", [Value3],
    "YouTube", [Value4]
  )

---

And this one is more user instances (aka visits):

SocialVennDiagramv2 = 
  var gp1Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Instagram")
  var gp2Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Twitter")
  var gp3Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "Facebook")
  var gp4Users = CALCULATETABLE(DISTINCT('UserSocial'[User Name]), 'UserSocial'[Platforms] = "YouTube")
  var x = 23

  RETURN SELECTCOLUMNS(
    {
      ( 1, 0, 0, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(gp1Users, UNION(gp2Users, gp3Users, gp4Users)) ), 0) ),
      ( 1, 1, 0, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp1Users, gp2Users), UNION(gp3Users, gp4Users)) ), 0) ),
      ( 1, 1, 1, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp3Users), gp4Users) ), 0) ),
      ( 1, 1, 1, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN INTERSECT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp3Users), gp4Users) ), 0) ),
      ( 0, 1, 0, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(gp2Users, UNION(gp3Users, gp4Users, gp1Users)) ), 0) ),
      ( 0, 1, 1, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp2Users, gp3Users), UNION(gp4Users, gp1Users)) ), 0) ),
      ( 0, 1, 1, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(INTERSECT(gp2Users, gp3Users), gp4Users), gp1Users) ), 0) ),
      ( 0, 0, 1, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(gp3Users, UNION(gp4Users, gp1Users, gp2Users)) ), 0) ),
      ( 0, 0, 1, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp3Users, gp4Users), UNION(gp1Users, gp2Users)) ), 0) ),
      ( 0, 0, 0, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(gp4Users, UNION(gp1Users, gp2Users, gp3Users)) ), 0) ),
      ( 1, 0, 1, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp1Users, gp3Users), UNION(gp2Users, gp4Users)) ), 0) ),
      ( 1, 0, 1, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(INTERSECT(gp1Users, gp3Users), gp4Users), gp2Users) ), 0) ),
      ( 1, 0, 0, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp1Users, gp4Users), UNION(gp2Users, gp3Users)) ), 0) ),
      ( 1, 0, 1, 0, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(gp1Users, gp3Users), UNION(gp2Users, gp4Users)) ), 0) ),
      ( 1, 1, 0, 1, COALESCE(CALCULATE(COUNTROWS('UserSocial'), 'UserSocial'[User Name] IN EXCEPT(INTERSECT(INTERSECT(gp1Users, gp2Users), gp4Users), gp3Users) ), 0) )
    },
    "Number of users", [Value5],
    "Instagram", [Value1],
    "Twitter", [Value2],
    "Facebook", [Value3],
    "YouTube", [Value4]
  )