Forum Discussion

JOAP's avatar
JOAP
Icon for Helper I rankHelper I
6 years ago

Add a column using LOOKUPVALUE but in the same table where I also want to lookup matching values

Hi, This one has been a real struggle for me. I'm trying to add a new column in my finance facts table using DAX function LOOKUPVALUE, but the tricky thing is that I only want to look for values in...
Need Help
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

Hi JOAP 

The column you need to check for unique values is IC_Match_Pay, since it's the one the filtering is being done on. Are you taking blank values into account? 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

JOAP's avatar
JOAP
Icon for Helper I rankHelper I
6 years ago

Sorry that doesn't help. In Excel a SUMIFS() or VLOOKUP() would easily fix this but I struggle in PBI Desktop.

Could maybe the SUMX() in combination with CALCULATETABLE() in PBI work?

 

Below didn't work:

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JOAP 

    You could create calculated columns

    Column = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))

    or 

    Column 2 = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]=EARLIER('Table'[IC_Match_Pay])&&'Table'[IC_Match_Pay]<>BLANK()))

     

    Or create a measure

    Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • JOAP's avatar
      JOAP
      Icon for Helper I rankHelper I
      6 years ago

      I'll go back a bit and explain my problem again because I'm really after a quick and lean solution not causing any burden on finding the matching data on any report visual. I also don't want to insert any calculated columns that really aren't necessary.

       

      I've created this measure to capture the internal stock value free from any filter applied:

      ISTO_Stock_ST0150 = CALCULATE('Measures Amounts'[Amount_YTD];Accounts[Account_Level0]="ST0150")
       
      I've also created this meausre to capture the internal margin free from any filter applied:
      ISTO_Margin_ST0151 = CALCULATE('Measures Amounts'[Amount_YTD];Accounts[Account_Level0]="ST0151")
       
      Then in a table visual I have made some filters to only caputure a certain period, actuality, currency etc....
      The below table data is fully ok but what I want is to match what the reporting company under Company_Code has reported as internal stock bought from another company with what that reporting company has reported as margin. The thing is that I want to show that on the same row. Example below where Company_Code "2001" reported 251 as internal stock bought from IC_Counter_Company_Code = "3001" and the matching margin is then 30.00 (%) found further below reported by Company_Code = "3001" and IC_Counter_Company_Code = "2001".
       
      So if I for example would filter this table visual only on Company_Code = "2001" then I would only get 1 Row with ISTO_Stock_ST0150 = 251 and ISTO_Margin_ST0151 = 30.00
       
      Best Regards,
      Johan