filter by group by

[benjellounm]

Hi Everyone, I need help creating a group by the table and use it to filter my data. The table has two columns; the name of the firm and Certification type and what I would like to do is create a table or column (whatever works) that shows whenever a firm is both DBE and SBE the result should show "SBE-DBE".

 Table

Firm name |  Certification

Firm A        |  SBE

Firm B        |  DBE

Firm A       |  DBE

I would like the result to be something like this

Firm name |  Certification

Firm A        |  SBE-DBE

Firm B        |  DBE

Thank you in advance 🙂

[Jihwan_Kim · Accepted solution]

New Table =

ADDCOLUMNS (

VALUES ( Data[Firm] ),

"@Certification",

CALCULATE(

IF (

{ "SBE" }

IN VALUES ( Data[Certification] )

&& { "DBE" } IN VALUES ( Data[Certification] ),

"SBE-DBE",

DISTINCT ( Data[Certification] )

)

)

)

https://www.dropbox.com/s/7r0xioyzurv5tw9/benjell.pbix?dl=0 

[Jihwan_Kim · Accepted solution]

New Table =

ADDCOLUMNS (

VALUES ( Data[Firm] ),

"@Certification",

CALCULATE(

IF (

{ "SBE" }

IN VALUES ( Data[Certification] )

&& { "DBE" } IN VALUES ( Data[Certification] ),

"SBE-DBE",

DISTINCT ( Data[Certification] )

)

)

)

https://www.dropbox.com/s/7r0xioyzurv5tw9/benjell.pbix?dl=0 

[benjellounm]

one more question is it possible to add a third if statement with the third column?

I would like to have something like this if the firm is DBE and ESB and Active then ESB-DBE 

Example

Firm name |  Certification  |  Status

Firm A        |  SBE               |   Active 

Firm B        |  DBE               |  Denied

Firm A       |  DBE                | Active

Result 

Firm name |  Certification  |  Status

Firm A        |  SBE-DBE       |  Active

Firm B        |  DBE               | Denied

[Jihwan_Kim · Accepted solution]

New Table V2 =

ADDCOLUMNS (

SUMMARIZE(Data,Data[Firm],Data[Status]),

"@Certification",

CALCULATE(

IF (

{ "SBE" }

IN VALUES ( Data[Certification] )

&& { "DBE" } IN VALUES ( Data[Certification] ),

"SBE-DBE",

DISTINCT ( Data[Certification] )

)

)

)

https://www.dropbox.com/s/7r0xioyzurv5tw9/benjell.pbix?dl=0 

[benjellounm]

Thank you very much 😊

[AllisonKennedy]

@benjellounm 

Do you have a Dim table for Firm already? 

Once you get that Firm table, relate it to the certifications table. Then you can add a column to it using DAX:

List of Certifications = CONCATENATEX(RELATEDTABLE(Certifications), Certifications[Certification], "-")

See sample file below signature.

[Fowmy]

@benjellounm 

Create the following table. This will summarize by Firm not only SBE-DBE but whatever multiple rows per firm.
Let me know if this works for you.

New Table = 
VAR __TABLE = 
    SUMMARIZE(
        TABLE8,
        Table8[Firm name],
        "CERTIFICATION",
        CONCATENATEX(
            DISTINCT( Table8[Certification] ),
            Table8[Certification], " - "
        )
    )
RETURN
    __TABLE