Forum Discussion
filter by group by
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 🙂
- New Table =ADDCOLUMNS (VALUES ( Data[Firm] ),"@Certification",CALCULATE(IF ({ "SBE" }IN VALUES ( Data[Certification] )&& { "DBE" } IN VALUES ( Data[Certification] ),"SBE-DBE",DISTINCT ( Data[Certification] ))))
- 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] ))))
6 Replies
- Jihwan_Kim
Super User
New Table =ADDCOLUMNS (VALUES ( Data[Firm] ),"@Certification",CALCULATE(IF ({ "SBE" }IN VALUES ( Data[Certification] )&& { "DBE" } IN VALUES ( Data[Certification] ),"SBE-DBE",DISTINCT ( Data[Certification] ))))Thank you very much 😊
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 | StatusFirm A | SBE-DBE | Active
Firm B | DBE | Denied
- Jihwan_KimNew 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] ))))
- 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 - AllisonKennedy
Community Champion
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.
