dax formula

[Kathir]

hi, what is the dax formula so that first column is earliest column with values and second column is the last column with values. For example, for first row is "Degree" and "Other Degree" and last second row is "Degree" and "Honours".

Thanks

[v-yajiewan-msft · Accepted solution]

Hi  @Kathir ,hello @audreygerred Thank you for your prompt reply!
For your requirement, we can create two measures as shown below:

FirstNonBlankColumn = 
SWITCH(
   TRUE(),
   'Table'[Degree]<>BLANK(),'Table'[Degree],
   'Table'[Bachelor]<>BLANK(), 'Table'[Bachelor],
   'Table'[Honours]<>BLANK(), 'Table'[Honours],
   'Table'[Master]<>BLANK(), 'Table'[Master],
   'Table'[Doctorate]<>BLANK(), 'Table'[Doctorate],
   'Table'[PhD]<>BLANK(), 'Table'[PhD],
   'Table'[Other Degree]<>BLANK(), 'Table'[Other Degree],
   BLANK()
)

LastNonBlankColumn = 
SWITCH(
   TRUE(),
   'Table'[Other Degree]<>BLANK(), 'Table'[Other Degree],
   'Table'[PhD]<>BLANK(), 'Table'[PhD],
   'Table'[Doctorate]<>BLANK(), 'Table'[Doctorate],
   'Table'[Master]<>BLANK(), 'Table'[Master],
   'Table'[Honours]<>BLANK(), 'Table'[Honours],
   'Table'[Bachelor]<>BLANK(), 'Table'[Bachelor],
   'Table'[Degree]<>BLANK(),'Table'[Degree],
   BLANK()
)

Result:

Best regards,

Joyce

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

[audreygerred]

You can rearrange the order of the columns in Power Query.

[Kathir]

no i need a formular such that i can sieve out the two values from all columns in data view