How to create a measure to count all the rows a text is contained in

[chat_peters]

Hello,

I have a simple data model and in my only fact table I have a column for buyer location and caller location. And I have another column where I concatenated the two columns. What I want to be able to do is to count all the rows for a select caller location if the concatenated column contains that selected value. This is the end result I want to achieve.  I can't use IN operator or USERELATIONSHIP (The actual model is quite large and we are on direct query in SSAS. Adding calculated tables are also not  supported. Is there anyway to achieve this with dax without having to make tables in the SQL server. Link to the file PowerBI File 

When I select Caller Location Guadalajara I want it to look in the concat column and count that row if it's in the concat column. 

thank you for taking the time to read this post

[chat_peters]

@m3tr01d I found out that I can't use containsstring function in SSAS directquery mode. I wrote this function below. However, it's not summing up the values correctly. Do you have any suggestions on how I can make this measure work?

Totalordersnew = var locations = IF(HASONEVALUE(CallerDim[Caller location]),VALUES(CallerDim[Caller location]))
                                RETURN CALCULATE(
                                            COUNTROWS(
                                                    FILTER('Order Fact',
                                                        OR(locations='Order Fact'[Caller Location],
                                                           locations='Order Fact'[Buyer Location]))),
                                                            All(CallerDim))

[chat_peters]

I tried this, and it solved my problem. Thank you for pointing me in the right direction 🙂

Measurecontainsstring = Var locations_x = SELECTEDVALUE(CallerDim[Caller Location])

                                         RETURN CALCULATE(
                                         COUNTROWS(
                                         FILTER(orderfact, CONTAINSSTRING(orderfact[concatcol],locations_x))),
ALL(Caller))

[chat_peters · Accepted solution]

I tried this

Measurecontainsstring = Var locations_x = SELECTEDVALUE(CallerDim[Caller Location])

                                         RETURN CALCULATE(
                                         COUNTROWS(
                                         FILTER(orderfact, CONTAINSSTRING(orderfact[concatcol],locations_x))),
ALL(Caller))

[chat_peters]

@m3tr01d If someone selects caller location = Rio from slicer, I want to be able to count all the rows where concat column contains Rio. The measure should be able to count order IDs 11, 14, 4 and 7. The answer should be 4. 

[m3tr01d]

Ok, I see.

If you use a slicer with Caller location coming from the CallerDim, this measure should give you what you want :

Buyer_Count = 
--Grab the selected Caller location from the slicer
VAR _Selected = SELECTEDVALUE( 'CallerDim'[Caller location] )

RETURN
CALCULATE(
    COUNTROWS( 'Order Fact' ),
    'Order Fact'[Buyer Location] = _Selected,
    ALL( 'CallerDim' )
)

[chat_peters]

Thank you for your quick response and thank you for taking the time to post a solutions. The measure works partially. I want to be able to pull all the row where the buyer or the caller responds to the selected location in caller dim. Right now for Rio it's only pulling up 2 records where the Buyer is from Rio. (it's only pulling up order ID 11 and 14 from the above picture not order IDs 4 and 7)

[m3tr01d]

Hi @chat_peters 

I just want to make sure I understand what you are trying to do.
If someone selects Caller Location = Rio from a Slicer, you want to count all the orders where
Caller Location is different then Rio and Buyer Location is equal to Rio. Is it right?