Hi everyone, I'm hoping someone can help.
I had a few tables consisted of million lines. Example below:
Table 1
| Account | Deal ID | Type | Production Year | Ship Year |
| AAA | 25654 | Sell-to | 2023 | 2023 |
| BBB | 21681 | Sell-through | 2022 | 2022 |
| CCC | 33547 | Design | 2023 | 2023 |
| AAA | 22258 | Sell-to | 2021 | 2023 |
| BBB | 35498 | Sell-to | 2023 | 2023 |
| CCC | 11568 | Partner-led | 2022 | 2022 |
| DDD | 11359 | Sell-through | 2023 | 2023 |
| DDD | 38794 | Design | 2021 | 2023 |
| AAA | 33545 | Sell-through | 2023 | 2024 |
Table 2
| Deal ID | New | Segment | Co-Work |
| 25654 | TRUE | X | Yes |
| 21681 | FALSE | Y | No |
| 33547 | TRUE | X | Yes |
| 22258 | TRUE | Z | Yes |
| 35498 | FALSE | X | Yes |
| 11568 | TRUE | Y | No |
| 11359 | FALSE | Y | No |
| 38794 | FALSE | Y | Yes |
| 33545 | TRUE | Z | No |
Now I wanted to summarize the column [Deal ID] and also add new KPI columns based on a few conditions:
I want to add columns to category the KPI each deal fulfiled, and an aggregated column as well.
For KPI-A, conditions would go as
{[Type] contains "sell-to" || "sell-through" } &&
[Segment] = "X" &&
[Ship Year] = 2023 &&
[Co-Work] = "Yes" &&
[New] = TRUE
For KPI-B, conditions are:
[Type] contains "Design" &&
[Production Year] = 2022 || 2023 &&
[Co-Work] = No
The desired outcome would be as follow:
| Deal ID | KPI-A | KPI-B | KPI Concatenated |
| 25654 | 1 | 0 | KPI-A |
| 21681 | 1 | 1 | KPI-A, KPI-B |
| 33547 | 0 | 1 | KPI-B |
| 22258 | 1 | 0 | KPI-A |
| 35498 | 1 | 1 | KPI-A, KPI-B |
| 11568 | 0 | 1 | KPI-B |
| 11359 | 0 | 1 | KPI-B |
| 38794 | 1 | 1 | KPI-A, KPI-B |
| 33545 | 0 | 0 | |
The DAX code I used right now is as follow:
EVALUATE
SUMMARIZECOLUMNS(
'Table1'[Deal ID],
KEEPFILTERS( TREATAS({2023}, 'Table 1'[Ship Year])),
"KPI-A", IF(
OR(SEARCH("Sell-to", 'Table 1'[Type],1,0)>0,
SEARCH("Sell-through",'Table 1'[Type],1,0)>0) &&
SEARCH("X", 'Table 2'[Segment],1,0)>0 &&
SEARCH("Yes",'Table 2'[Co-Work],1,0)>0 &&
'Table 2'[NEW] = TRUE,
"1", "0"),
"KPI-B", IF(
SEARCH("Design", 'Table 1'[Type],1,0)>0 &&
[Production Year] IN {2022,2023} &&
SEARCH("No",'Table 2'[Co-Work],1,0)>0,
"1", "0")
"KPI Concatenated", COMBINEVALUES( ", ", [KPI-A], [KPI-B])
)
However, it gave me the error message "A single value for column xxx in table xxx cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregator such as min, max, count, or sum to get a single result. " It seems the problem is located from the line where the code written after "KPI-A", IF( "
I tried to use COUNTROWS(FILTER( xxx, CONTAINSSTRING(xxx,xxx)))>0 but when it comes to multiple OR and AND conditions, it gave me the same error.
Can anyone kindly help me? Thanks!
