locked
Find if corresponding value in lookup table RRS feed

  • Question

  • I have a table that is a data table. It has contract Id and is connected to a lookup table that also has contract ID (1 to 1 relationship). How can I check that all the values in this data table have a corresponding value in the lookup table? Thanks. 
    Thursday, October 27, 2016 12:21 AM

Answers

  • A few ways you could do it.

    One is to create a measure:

    MissingLookup := COUNTROWS ( CALCULATETABLE ( Lookup, Data ) ) = 0

    or

    MissingLookup := ISEMPTY ( CALCULATETABLE ( Lookup, Data ) )

    Then put MissingLookup in a PivotTable with Data[Contract ID] on the rows.

    MissingLookup measure will evaluate to TRUE for any Data[Contract ID] with no corresponding row in Lookup.

    Alternatively, add a calculated column to your Data table:

    = CALCULATE ( COUNTROWS ( Lookup ) )

    This will evaluate to 1 for rows that have a corresponding value in Lookup, or blank otherwise.


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger



    • Edited by Owen Auger Thursday, October 27, 2016 7:22 AM
    • Marked as answer by AlexMartini Thursday, October 27, 2016 11:05 PM
    Thursday, October 27, 2016 7:03 AM

All replies

  • A few ways you could do it.

    One is to create a measure:

    MissingLookup := COUNTROWS ( CALCULATETABLE ( Lookup, Data ) ) = 0

    or

    MissingLookup := ISEMPTY ( CALCULATETABLE ( Lookup, Data ) )

    Then put MissingLookup in a PivotTable with Data[Contract ID] on the rows.

    MissingLookup measure will evaluate to TRUE for any Data[Contract ID] with no corresponding row in Lookup.

    Alternatively, add a calculated column to your Data table:

    = CALCULATE ( COUNTROWS ( Lookup ) )

    This will evaluate to 1 for rows that have a corresponding value in Lookup, or blank otherwise.


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger



    • Edited by Owen Auger Thursday, October 27, 2016 7:22 AM
    • Marked as answer by AlexMartini Thursday, October 27, 2016 11:05 PM
    Thursday, October 27, 2016 7:03 AM
  • Yeah, I ended up doing the third of your options. 

    Thanks Owen. I see you are a CFA. Do you use powerpivot for financial modeling? Do you know where I can see good samples?

    Thursday, October 27, 2016 11:05 PM
  • That's good.

    At the moment, I use PowerPivot & Power BI for analyzing forecasts that are already in a data structure, i.e. the modelling has already happened before being loaded as a data source.

    I have played around building forecast models with PowerPivot but not used it seriously for this.

    I have seen some examples of "forecasting" formulas and P&L analysis that could be a starting point:

    http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

    http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax/

    http://tinylizard.com/financial-statements-in-power-pivot/





    Owen Auger, CFA https://nz.linkedin.com/in/owenauger

    Saturday, October 29, 2016 9:46 AM