Forum Discussion

Kathir's avatar
Kathir
Frequent Visitor
2 years ago
Solved

dax formula

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

3 Replies

    • Kathir's avatar
      Kathir
      Frequent Visitor
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.