topic Re: Alternative to Lookupvalue to use in virtual tables in DAX Commands and Tips

Alternative to Lookupvalue to use in virtual tables

Anonymous — Wed, 22 May 2024 14:05:40 GMT

Hi!

I need help creating a column inside a virtual table, so I cannot use a lookupvalue as a physical table is required...

This a dummy data to show what I need. This would be my Sales Table, where "Period" is not in date format, but text:

Here, in period 1, the Tshirt was classified as Shirt, but in the next period was reclassified as Clothes. This is making my future calculations look like they are 2 different products, while they are the same. I can create a summarize table without Category, but I need to be able to analyse that as well so I need to include it.

I could hard code a change, but each month some products get changed, and I cannot waste my time to look for them and change them. I'm trying now to add the column after the initial "ADDCOLUMNS( SUMMARIZE", but I cannot use lookupvalue to do it.

The result I'm looking for would be something like this, were if there is any discrepancy in the category, the one in Period 1 should be replaced with the category from Period 2 (the most recent one).

This way they would be treated as just 1 product to make further analysis like Actual - Outlook.

This virtual table gets calculated according to the Periods that the user selects in the slicer, so I cannot create a table for this to change the category because depending on the periods, the result of the category might be different.

Is there a way I can create this column "Category", checking the product name and fetching the category from the Period 2?

Thank you very much!!

Re: Alternative to Lookupvalue to use in virtual tables

AntrikshSharma — Wed, 22 May 2024 16:46:58 GMT

Anonymous What happens if in Period 3 T-Shirt is again categorized as Shirt what should be the logic for that row?

Re: Alternative to Lookupvalue to use in virtual tables

Anonymous — Thu, 23 May 2024 07:43:26 GMT

Hi AntrikshSharma

Thank you for replying!! The logic is that if the product gets reclassified, it always takes the classification of the latest period chosen.

If we have Period 3 as Shirt. Then if the user chooses Period 3 and 2 to compare, the Category should be Shirt, as it is the last one the one that remains.

If the user picks Period 3 and 1, it would be Shirt because it's the same category, but if Period 2 and 1 are chosen, then it would be Clothes, as Period 2 is the last one there.

I hope this makes what I need a bit more clear.

Thank you very much!!

Re: Alternative to Lookupvalue to use in virtual tables

AntrikshSharma — Sun, 26 May 2024 07:28:52 GMT

Anonymous Try this:

Category Corrected = IF ( ISINSCOPE ( 'Table'[Period] ), VAR MaxSelectedPeriod = CALCULATE ( MAX ( 'Table'[Period] ), ALLSELECTED ( 'Table' ) ) VAR MostRecentCategory = CALCULATE ( VALUES ( 'Table'[Category] ), 'Table'[Period] = MaxSelectedPeriod, REMOVEFILTERS ( 'Table' ) ) RETURN MostRecentCategory )

Re: Alternative to Lookupvalue to use in virtual tables

Anonymous — Tue, 28 May 2024 08:37:07 GMT

Hi AntrikshSharma

Can you share the pbix here to see it? I'm trying to adapt your code to add it to my virtual table but I'm having some issues. Maybe if I see it in the file I can adapt it better.

Million thanks for your help!!

Re: Alternative to Lookupvalue to use in virtual tables

AntrikshSharma — Tue, 28 May 2024 10:53:28 GMT

Anonymous You can download from here: https://drive.google.com/file/d/1e90MuQ8b316hgoBS-n8l9kTuHGNJUD6B/view?usp=sharing