CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visuals
Anurag Yadav
Anurag Yadav

Reputation: 3

How to arrange the data in PowerBi using Dax?

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.

Upvotes: 0

Views: 58

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

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

Upvotes: 0

Related Questions