DAX expression needed to create calculated column where vLookup kind of functionality is needed

[Anonymous]

Need help in classifying data in a Calculated Column using DAX.

Below is How my data looks:

Unique Data - This data is unique and only one of it will be available in the dataset

Parent Data - These are parent data. They may have multiple Unique data under them as their child as they are related. Also if the column is NULL then the correcponding Unique data is a Parent by itself since its not having data under it.

Calculated column Output - Here i need to use DAX to classify the 'Unique Data' as Parent or Child.

Rules for the classification -

1. If 'Unique Data' column has NULL value in 'Parent Data', then its a Parent

2. If 'Unique Data' column is available in 'Parent Data' as some other data' parent, then its a Parent.

3. If 'Unique Data' column is not available in 'Parent Data', then its a Child

All the above rules will get the all the Unique data Sorted into 2 categories and since they need to refer the Parent data like a vlookup i need help, since it will take time to do calculation in actual data set i need it optimised as well. If a Unique data has a correspoding NULL in Parent data then it does not need to be checked like a vlookup, and Unqiue data which has a Parent data needs to be compared to the non-NULL values in Parent data to be categorised.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and sample data, I can clearly understand your requirement, I think you can try to create a calculated column like this to achieve your requirement:

Output =
var _parentdate=SELECTCOLUMNS(FILTER('Table',NOT(ISBLANK([Parent Data]))),"1",[Parent Data])
return
SWITCH(
    TRUE(),
    ISBLANK([Parent Data]),"Parent",
    [Unique Data] in _parentdate,"Parent",
    NOT([Unique Data] in _parentdate),"Child",
    BLANK())

And you can get what you want, like this:

You can download my test pbix file below

If this you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Perhaps something like:

Column = 
  SWITCH(TRUE(),
    [Parent Data] = "NULL","Parent",
    [Unique Data] IN SELECTCOLUMNS(FILTER('Table',[Parent Data]="NULL"),"Parent",[Unique Data]),"Parent",
    "Child"
  )