Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

VLOOKUP Workaround in PowerBI

Hi all, 

 

I have a pricing dataset which is broken down by Quote Opportunity and then Scenario. Each Quote can have multiple scenarios. For a scenario to be considered 'complete' it needs to contain a minimum of 2 specific components, but might contain 3. I wanted a pivot that contains only the quotes that are complete. In Excel, I got around this by creating a pivot that had the product component as both a column and a value (count), and then performing 3 vlookups that reference the Scenario in the raw data, and the count of product in the pivot. Then a final column that said If value of Product A and value of Produc B >0, 'True' otherwise False. Then set this column as the filter for a new pivot, and it worked fine.

 

However, that was using an exported Excel file, and I am tasked with creating a report in PowerBI that connects directly to the Oracle Database from which this data is taken, and now I don't know how I can replicate this solution in PowerBI.  Screenshot below for context:

 

This one is broken down by country instead of scenario. But in this example, Denmark and GIbraltar should be excluded because they don't contain the two product components needed (E-Access - OVC, Wholesale UNI)

 

Pivot configuration that worked (after the vlookups)

 

 

 

 

 

Need Help

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi,  Anonymous 

    You can try to apply these fields to the PowerBI matrix visual and then add filter conditions in visual filter pane.

    If possible, please share you sample data and expected result for further research.

     

    Best Regards,
    Community Support Team _ Eason



     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Sorry for the late reply, bedridden with Corona but have since recovered. I've created a dummy excel file with no sensitive data which shows exactly how I'd like the matrix to appear in PowerBI (and the Excel workaround I did to show it) as well as an incorrect matrix style. The issue is that I'm not sure how to pull this workaround into PowerBI. I could just do the transformation on the data in Excel first and use the FilterVal column as a filter in PBI, but this is not a good solution because I am tasked with connecting directly to the database from PBI so I cannot do my transformations in Excel first.

     

    EDIT: You'll need to open the file in Excel, Google Docs doesn't pivot correctly

    https://docs.google.com/spreadsheets/d/1Z5Pj8bVuGQOdfs57De0E8BdvgWpZRt4q/edit?usp=sharing&ouid=106225916084559153908&rtpof=true&sd=true