topic Re: Measure to count rows which contain multiple test strings in DAX Commands and Tips

Measure to count rows which contain multiple test strings

stu3000 — Fri, 03 Feb 2023 23:57:11 GMT

Dear PowerBI Community,
I'm looking for a dax-measure that counts the rows of th comissions-table which contain multiple codes that I have selected with a slicer.

The result in this case would be "2"

So far I have found the containsstrings command, but I don't know how to combine the selected codes

Do you have any advise?

Re: Measure to count rows which contain multiple test strings

amitchandak — Sat, 04 Feb 2023 02:08:50 GMT

stu3000 , Try a approch like this

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c

Re: Measure to count rows which contain multiple test strings

stu3000 — Sun, 05 Feb 2023 21:22:28 GMT

Dear amitchandak

thanks for your reply. It's a good approach, however I couldn't really solve the problem with it yet. Let me try to explain it better with the following example:

I have

the "CodesTable" with all the necessary Codes, which is also the data for the slicer.
the CommissionsTable with the Commission Numbers and the Codes which are basically all in on Column. In real life it's a huge table which I actually don't want to change if possible.

So far I have the following measure which works fine when only one code is selected:

AffectedCommissions =

var selectedcode = SELECTEDVALUE(CodesTable[Codes])

RETURN

calculate(COUNT(CommissionsTable[CommissionNo]),CONTAINSSTRING(CommissionsTable[Codes],selectedcode))

However, if more than one code is selected with the slicer, only the rows which contain the selected codes should be counted. For example, if Codes 3, 4 and 5 are selected, then the result must be 2 (CommissionNo 3 and 6).

Furthermore, the Codes in the orders are not alway in the same order.
Is it possible to adjust the measure accordingly?

Re: Measure to count rows which contain multiple test strings

Anonymous — Mon, 06 Feb 2023 06:20:35 GMT

Hi stu3000 ,

Please try below steps:

1. below is my test table

Table1:

Table2:

2. create a measure with below dax formula

Measure = VAR _a = ADDCOLUMNS ( 'Table2', "Flag", IF ( SUMX ( ADDCOLUMNS ( SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ), "Max Codes", IF ( CONTAINSSTRING ( [Codes], [Selected] ), 1, 0 ) ), [Max Codes] ) = COUNTROWS ( SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ) ), 1, 0 ) ) RETURN SUMX ( _a, [Flag] )

3. add a slicer with field, add a cred visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure to count rows which contain multiple test strings

stu3000 — Mon, 06 Feb 2023 13:51:49 GMT

this works perfectly. Thanks, Binbin Yu!!