Measure to count rows which contain multiple test strings

[stu3000]

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? 

[amitchandak · Accepted solution]

@stu3000 , Try a approch like this

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

[stu3000 · Accepted solution]

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?

[stu3000 · Accepted solution]

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?

[Anonymous · Accepted solution]

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.

[stu3000]

this works perfectly. Thanks, Binbin Yu!!

[amitchandak · Accepted solution]

@stu3000 , Try a approch like this

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