Forum Discussion

mcam99's avatar
mcam99
Frequent Visitor
8 years ago
Solved

Add Column Group by

I have the following query to create groups where an entity exists in more than 1 group

 

ie

User 1, Mobile

User 1, Desktop

=

User 1, Mobile,Desktop

 

My issue is where I have a different primary groups but the same secondary group it creates the following 

 

Facebook,Mobile,Mobile
Mobile
Mobile,Desktop
Mobile,Mobile

 

Where as I want the results grouped like

 

Facebook,Mobile
Mobile
Mobile,Desktop

 

Below is the current DAX syntax I am using, can anyone help ?

 

Thanks in advance

 

 

Attributes = ADDCOLUMNS(
GROUPBY(
Control,
Control[UUID]
),
"PrimaryGroup",
CALCULATE(
CONCATENATEX(Control, Control[PrimaryGroup], ","),Control[IsFootfall] = 0),
"SecondaryGroup",
CALCULATE(
CONCATENATEX(Control, Control[SecondaryGroup], ","),Control[IsFootfall] = 0)
)

 

 

 

  • mcam99

     

    Try this one

     

    Attributes =
    ADDCOLUMNS (
        GROUPBY ( Control, Control[UUID] ),
        "PrimaryGroup", CALCULATE (
            CONCATENATEX (
                VALUES ( Control[PrimaryGroup] ),
                Control[PrimaryGroup],
                ",",
                Control[PrimaryGroup]
            ),
            Control[IsFootfall] = 0
        ),
        "SecondaryGroup", CALCULATE (
            CONCATENATEX (
                VALUES ( Control[SecondaryGroup] ),
                Control[SecondaryGroup],
                ",",
                Control[SecondaryGroup]
            ),
            Control[IsFootfall] = 0
        )
    )

5 Replies

  • mcam99's avatar
    mcam99
    Frequent Visitor

    I have the following query to create groups where an entity exists in more than 1 group

     

    ie

    User 1, Mobile

    User 1, Desktop

    =

    User 1, Mobile,Desktop

     

    My issue is where I have a different primary groups but the same secondary group it creates the following 

     

    Facebook,Mobile,Mobile
    Mobile
    Mobile,Desktop
    Mobile,Mobile

     

    Where as I want the results grouped like

     

    Facebook,Mobile
    Mobile
    Mobile,Desktop

     

    Below is the current DAX syntax I am using, can anyone help ?

     

    Thanks in advance

     

     

    Attributes = ADDCOLUMNS(
    GROUPBY(
    Control,
    Control[UUID]
    ),
    "PrimaryGroup",
    CALCULATE(
    CONCATENATEX(Control, Control[PrimaryGroup], ","),Control[IsFootfall] = 0),
    "SecondaryGroup",
    CALCULATE(
    CONCATENATEX(Control, Control[SecondaryGroup], ","),Control[IsFootfall] = 0)
    )

     

     

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion

    mcam99

     

    Please Give this a shot

     

    Attributes =
    ADDCOLUMNS (
        GROUPBY ( Control, Control[UUID] ),
        "PrimaryGroup", CALCULATE (
            CONCATENATEX ( VALUES ( Control[PrimaryGroup] ), Control[PrimaryGroup], "," ),
            Control[IsFootfall] = 0
        ),
        "SecondaryGroup", CALCULATE (
            CONCATENATEX (
                VALUES ( Control[SecondaryGroup] ),
                Control[SecondaryGroup],
                ","
            ),
            Control[IsFootfall] = 0
        )
    )
    • mcam99's avatar
      mcam99
      Frequent Visitor

      Thanks Zubair that is really useful, it has grouped most of them -  although its still not grouping I would expect.

       

      these are my results

       

      Desktop
      "Desktop,Mobile"
      Facebook
      "Facebook,Desktop"
      "Facebook,Desktop,Mobile"
      "Facebook,Mobile"
      "Facebook,Mobile,Desktop"
      Mobile
      "Mobile,Desktop"

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion

        mcam99

         

        Try this one

         

        Attributes =
        ADDCOLUMNS (
            GROUPBY ( Control, Control[UUID] ),
            "PrimaryGroup", CALCULATE (
                CONCATENATEX (
                    VALUES ( Control[PrimaryGroup] ),
                    Control[PrimaryGroup],
                    ",",
                    Control[PrimaryGroup]
                ),
                Control[IsFootfall] = 0
            ),
            "SecondaryGroup", CALCULATE (
                CONCATENATEX (
                    VALUES ( Control[SecondaryGroup] ),
                    Control[SecondaryGroup],
                    ",",
                    Control[SecondaryGroup]
                ),
                Control[IsFootfall] = 0
            )
        )