topic Need to return RELATED value for SKUs with Alternative IDs in DAX Commands and Tips

Need to return RELATED value for SKUs with Alternative IDs

AlexAlberga727 — Wed, 12 Feb 2020 18:54:58 GMT

Folks -

I have a sistuation were I need to return the total number of Orders for a particular SKU. However - If the SKU has an Alternative SKU, I would like to see the number of orders for the Alternative SKU instead.

So far - I have pulled the neccessary data into Power bI, and have set up the relationships accordingly.

I have a ProductTable which contains both the SKU, and the Alternative SKU. I wanted to use the RELATED DAX to return the # of Orders - Which works. However, the SKUs that have alternative SKUs return a 0 for # of orders. This is accurate, and as expected. However - because these values have an alternative SKU - Is there a way to lookup to alternative SKUs On PO# instead?

Where I get stuck -

QTY on PO = IF( ISBLANK ( PRODUCTTABLE[ALTERNATIVEID ), RELATED ( QTYonPO ),

Im not sure now how to point it to the AlternativeID.

Let me know if any ideas are out there, or if any addtional information is needed.

Thanks!

Re: Need to return RELATED value for SKUs with Alternative IDs

AlexAlberga727 — Wed, 12 Feb 2020 18:58:00 GMT

Here is an example of what I would need. - Where Alternative SKUs ISBLANK - Simply gather the On Po #s using the RELATED DAX. This works with no issue. The problem I have is when Alternative SKU is populated - How can I then tell it to gather the number "On Order" for the Alternative SKU, NOT the SKU,

Re: Need to return RELATED value for SKUs with Alternative IDs

AlexAlberga727 — Wed, 12 Feb 2020 19:27:41 GMT

QTYonPOv2 = IF( ISBLANK( dProductTable[PartID] ), RELATED( 'dInvVALUE (Keep Up-to-Date)'[QTY on PO] ), LOOKUPVALUE( 'dInvVALUE (Keep Up-to-Date)'[QTY on PO], 'dInvVALUE (Keep Up-to-Date)'[ProductID], dProductTable[PartID] ))

I feel this may be to solution - However, I am new to LOOKUPVALUE. Not sure if I have configured it correctly. Results are currenlty are Blank when PARTID is NOT BLANK. It is returning the RELATED values correctly.

dInVALUE Table contains the [QTY on PO] #s.

dInvValue[ProductID] is where dProductTable[PartID] can be found.

Re: Need to return RELATED value for SKUs with Alternative IDs

AlexAlberga727 — Wed, 12 Feb 2020 19:33:28 GMT

amitchandak az38 Anonymous JarroVGIT

Either of you Super Contributors able to assist with this one? Please/Thanks!

Re: Need to return RELATED value for SKUs with Alternative IDs

Anonymous — Thu, 13 Feb 2020 06:04:56 GMT

Column= if(isblank(max(table[alter id])),ralated(table1[order]),table[order])

Here the logic is ...if alter id is blank then take order from related table else take order from same table.

Incase at first load your alter id is empty...and later you have alter id for particular row then you have to refresh your data..the abouve formula will give you expected result.

I am assunming here table and table 1 is connected.

Thanks
Pravin

Re: Need to return RELATED value for SKUs with Alternative IDs

AlexAlberga727 — Thu, 13 Feb 2020 12:59:50 GMT

Unfortuneatly Im trying to tie in data from a flat file that is updated everyday. This file contains a list of SKUs on a # of Products on Order from the factory. I need to pull this number into my reporting. However, all SKUs are listed in a single column. This column contains both, what we're calling SKUs and Alternative SKUs.

Example - SKU "123" has an Alternative SKU "123-Alternative". Both SKUs are found within the same column. However, SKU 123's "On PO" # is 0, whereas 123-Alternative contains 100. The formula I create would need to return 100 for SKU 123, because it contains an alternative SKU.

This needs to ocurr for a couple of SKUs (~10), so i created a table which contains the relationship between SKUs and their Alternative SKU.

I have created a couple different calculated columns in data view to make things easier.

SKU	Alternative SKU	QTY on PO
123	123-Alternative	100
123-Alternative		100
456		10
789-Alternative		100
789	789-Alternative	100

File which contains the QTY on PO is loaded into its own table, and the table containing the above is linked via ProductID.

I feel like i need to either use LOOKUPVALUE or... have RELATED point to the alternativeID when gathering QTYonPO opposed to the SKU the record is truley for.

I hope this clears things up, and not make them more complicated/confusing. Please let me know if you have any ideas.

Thanks!