DAX Script for TOTAL and CONCAT

[su1337]

I need help writing a DAX script in PowerBI to take care of the following problems:

1. I need a TOTAL column that will sum the number of 'Runs' for each 'Group'. --> TOTAL for group A would be 5

2. I need to combine like terms in the 'Type' column if the 'Group' and 'Project' are identical. --> So Group A would be listed as

A

Blue

Web, Mobile

5

Group Project Type Runs --> these are the column headers

A	Blue	Web	2
A	Blue	Mobile	3
B	Orange	Mobile	1
C	Red	Web	4
C	Red	Web	3
C	Pink	Web	1
D	Yellow	Mobile	2

[v-yulgu-msft]

Hi @su1337,

Alternatively, you can new a calculated table.

Table5 =
SUMMARIZE (
    table4,
    table4[Group],
    table4[Project],
    "Type", CONCATENATEX ( table4, table4[Type], "," ),
    "Runs", SUM ( table4[Runs] )
)

Best regards,

Yuliana Gu

[ChrisMendoza]

@su1337,

If you are able to let the visual do some of the work for you, you can try the following as a measure:

Measure = 
IF(
    HASONEFILTER(TableName[Group]),
    CONCATENATEX(TableName,TableName[Type],", ",TableName[Type],ASC)
)

This will produce a visual Matrix or Table respectively as;

[su1337]

The problem is that I need this script to run against the selected visual filters. I have the entire data filtered by date and other parameters so the script needs to take that into account.

I wasnt sure if there was a way to save a visual as a table because that could help the problem I am experiencing. 

[v-yulgu-msft]

Hi @su1337,

That case, you can try ChrisMendoza's suggestion to create a measure which would return result conditionally taking slicer selection into account.

Regards,

Yuliana Gu

[su1337]

No this didn't work, unfortunately. 

[v-yulgu-msft]

Hi @su1337,

Would you please share your sample .pbix file to make your scenario more clear?

Regards,

Yuliana Gu