Help Needed: SEARCH within SUMMARIZECOLUMNS

ihungko — Wed, 26 Jul 2023 10:15:38 GMT

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!

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS

ERD — Wed, 26 Jul 2023 18:47:10 GMT

Hi ihungko ,

You need to double check your conditions and the result you want to achieve.

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS

ihungko — Thu, 27 Jul 2023 03:03:19 GMT

Thank you so much, ERD.

By the way, if one of my condition would be that check if rows in a certain column contains *keywords, how should I write the DAX?

Can I use the following DAX along with other && || conditions?

CONTAINSSTRING( SELECTEDVALUE( [Column] ), "*Keywords") = TRUE

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS

ERD — Thu, 27 Jul 2023 05:45:24 GMT

Yes,

topic Re: Help Needed: SEARCH within SUMMARIZECOLUMNS in DAX Commands and Tips

Help Needed: SEARCH within SUMMARIZECOLUMNS

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS

Re: Help Needed: SEARCH within SUMMARIZECOLUMNS