Need help with a scenario.

[SanketBhagwat]

Need to add new column to Tab table - TabIndexParent.

• If ParentTab is not 00000000-00000... then lookup ParentTab ID in Tab column and populate TabIndexParent column with TabIndex.
• ie, see highlighted rows
• in this example TabIndexParent would be populated = 1600 for all rows that have ParentTab = 5915D......
• If ParentTab = 0000000-0000.... then populate TabIndexParent = TabIndex
• 0000... means they have no parent, only 1 level. so we use TabIndex as TabIndexParent

• ParentTab	Tab	TabIndex	TabIndexParent
  00000000-0000-0000-0000-000000000000	0BBCFD72-2325-4D48-8D0B-A44463BB011D	2100	2100
  00000000-0000-0000-0000-000000000000	5915D9B3-1527-4B08-8819-96DF21C84B6B	1600	1600
  6565A8D4-A939-430F-AEDC-4A7F85293B36	0F2CB553-0796-4DF0-99C2-2F5CF7F1EBBA	3000	1000
  0F2CB553-0796-4DF0-99C2-2F5CF7F1EBBA	6565A8D4-A939-430F-AEDC-4A7F85293B36	1000	3000

@amitchandak @parry2k @selimovd  @Fowmy  Please help.

[Anonymous]

Hi @SanketBhagwat ,

you can use the following dax for the requirement 

TabIndexParentnew =

Switch('Table'[ParentTab],

"00000000-0000-0000-0000-000000000000",'Table'[TabIndex],

LOOKUPVALUE('Table'[TabIndex],'Table'[ParentTab],'Table'[Tab]

))

Regards,

Aditya

[SanketBhagwat]

Hi @Anonymous .

My column doesn't contain unique values, so your DAX is not working and giving an error as "A table of multiple values was supplied where a single value was expected".

[selimovd]

Hey @SanketBhagwat ,

your description is very confusing.

Here what I understood:

- You want a new column

- The content should the content of ParentTab when ParentTab is not "00000000-0000-0000-0000-000000000000".

- When ParentTab is "00000000-0000-0000-0000-000000000000" you want the content of TabIndexParent

Did I get that right?

Best regards

Denis

[SanketBhagwat]

1)When Parent Tab is "00000000-0000-0000-0000-000000000000",then it will directly return its correspondingTabIndexvalue.

2)When ParentTab is not equal to "00000000-0000-0000-0000-000000000000", then it will look for that ID
 in Tab column and return the 'TabIndex' for that 'Tab' column.

So suppose if I have 'ParentTab' value as 22525-272625-282827, then it will search that value in 'Tab' Column and return 'TabIndex' of that value in the new column.

[SanketBhagwat]

For example, in the data which I provided, TabIndexParent is the new calculated column which we want to calculate.

So when ParentTab is "00000000-0000-0000-0000-000000000000",the new column is returning its corresponding TabIndex value which is 2100.

When ParentTab is "6565A8D4-A939-430F-AEDC-4A7F85293B36", it is searching for that value in 'Tab'

column and returning value as 1000.

Hope that will make things clear.

Same when ParentTab is "0F2CB553-0796-4DF0-99C2-2F5CF7F1EBBA", it is returning value as 3000.

[selimovd]

Hey @SanketBhagwat ,

thank you for the explanation. Now I got what you need.

Please try the following calculated column:

TabIndexParent NEW =
VAR vParentTab = 'Tab Table'[ParentTab]
RETURN
    SWITCH(
        TRUE(),
        'Tab Table'[ParentTab] = "00000000-0000-0000-0000-000000000000", 'Tab Table'[TabIndex],
        CALCULATE(
            MAX( 'Tab Table'[TabIndex] ),
            ALL( 'Tab Table' ),
            'Tab Table'[Tab] = vParentTab
        )
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[SanketBhagwat]

Hi @selimovd .

Thanks for your reply.

That DAX worked, but didn't gave the perfect output.

Appreciate your help.

[selimovd]

Hey @SanketBhagwat ,

and what exactly is still wrong?

Give me some feedback, then I can change the formula.

Best regards

Denis

[SanketBhagwat]

Hi @selimovd .

I can't share the data, so I took some sample data and posted that in this blog.

For some Tab ID's there are multiple TabIndex values.

If we use MAX function to get the maximum value out of those, then we are getting wrong output.

We are getting correct output for  "00000000-0000-0000-0000-000000000000",but not for other values.

I think wee will need to create a custom Key column by making use of any other column to bifuracte.

For example, we can make use of any ID column in the table and create a key column using  Tab ID and ID from that column and then use LOOKUPVALUE function to search that value in the Key column.

Regards,

Sanket Bhagwat

[selimovd]

Hey @SanketBhagwat ,

when you have multiple Tabindex values, how do you know which one is the correct one?

If there is no logic to do the right selection, you have to find another criteria to get a correct match.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic