Forum Discussion

markpatton's avatar
markpatton
Icon for Helper I rankHelper I
4 years ago

Ignoring relationships when using lookup value or alternative

I have two tables that I want to cross reference. The tables are also used for other funcions so they have active relationships based on dates, etc.

 

I want to do a lookup from one table to another to cross reference transactions. Problem is that the cross reference may involve rows that are out of the selection range. The ideal is to use lookupvalue but ignore relationships.

 

The first thing I tried was lookupvalue but found it worked only on finding the cross reference records only in the current selection based on the relationships in the model. If I removed the relationship I got all the entries. So for this option I need to be able to ignore the relationships.

 

The second thing I tried is to set up a table in a DAX column calculation that selects all records. I found that this does find all the entries but now I need to select a text value of the rows in the table. How do I do this.

 

The following works to return the line amount for the filtered table, What I need to do is return a text value for a column of the row. 

 

VAR _matching_bills = filter(Bills, Bills[GLTxn Line Item Description] = Invoices[GLTxn Line Item Description])
VAR _match_count = format(countrows(_matching_bills), "General Number")
return if (_match_count = "1", format(sumx(_matching_bills, [GLTxn Line Amount]), "General Number"), "Not Found")
 
I tried the MAX function but can't figure out how to use it on a table variable.
 

 

Need Help

1 Reply

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,
    Without seeing your data model it's hard to work out exactly what you're trying to do however you might find...

    CROSSFILTER - DAX Guide with the 3rd parameter set to "None" within a CALCULATE statement.

    However I think more likely you want to remove any filters coming from the date table?

     

    Eg
    CALCULATE (
    [Your Measure],
    REMOVEFILTERS(Date)
    )