Forum Discussion

adriansuteu's avatar
adriansuteu
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Filter measure based on related table column value

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,
 
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

2 Replies