Filter measure based on related table column value

[adriansuteu]

Hello.

I have the following situation: I have a measure that needs to be filtered based on the column value from a related table. More specifically I want to exclude the amount of money from invoice numbers that contain "ctr".  I know that there are other ways of doing this but I can not use the filters on the report/this page/this visual section because the user will lately be able to choose between this new measure and another measure to show in the table. I can not use columns instead of measure because this measure is the source of a lot of calculations and it is also another variable measure.

The source measure looks like this:

Amount Left (Selected) =

VAR VAT = [Amount Left Artif with VAT (for table) aged rec]

VAR woVAT = [Amount Left Artif (for table) aged rec]

VAR SelectedValue = MIN(VAT_Filter[Id])

RETURN

IF (HASONEVALUE(VAT_Filter[Value]),

    SWITCH (SelectedValue,

        1, VAT,

        2, woVAT))

After selecting this the user is supposed to select another indicator on the same system. With or without Invoices (from a related table) that contain "ctr" in invoice number (string column).

I was trying to do this but it does not work:

Amount without Contracts = CALCULATE([Amount Left (Selected)],FILTER(Courses,Courses[Invoice] <> "%ctr%"))

Can you please help me with the DAX so I can filter an already established measure based on a column in a related table?

Thank you,

[v-lili6-msft · Accepted solution]

Hi, @adriansuteu 

Just add just your formula as below:

Amount without Contracts = CALCULATE([Amount Left (Selected)],FILTER(Courses,SEARCH("ctr",Courses[Invoice],1,0)=0))

or

Amount without Contracts 2 = CALCULATE([Amount Left (Selected)],FILTER(Courses,FIND("ctr",Courses[Invoice],1,0)=0))

Result:

Regards,

Lin

[amitchandak]

Power Bi has a search function

https://community.powerbi.com/t5/Desktop/LIKE-function-in-a-measure/td-p/192651

https://docs.microsoft.com/en-us/dax/search-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601