Forum Discussion

apelleti's avatar
apelleti
Helper I
3 years ago
Solved

DAX expression question

Hi everyone,    I'm looking for a DAX forumla that will return the column below "Significant Line". If a line contains at least one significant segment (Significant Segment = Yes), then each entry ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Update like this:

    Column =
    VAR _line = TableName[Line]
    VAR SigSeg =
    CALCULATE(
        MAX(TableName[Significant Segment]),
        TableName[Line] = _line,
        ALL()
    )
    RETURN
    IF(SigSeg="Yes", "Yes", "No")
     
    tried and it worked like this:
FreemanZ's avatar
FreemanZ
Super User
3 years ago

Update like this:

Column =
VAR _line = TableName[Line]
VAR SigSeg =
CALCULATE(
    MAX(TableName[Significant Segment]),
    TableName[Line] = _line,
    ALL()
)
RETURN
IF(SigSeg="Yes", "Yes", "No")
 
tried and it worked like this:
apelleti's avatar
apelleti
Helper I
3 years ago

FreemanZ like a charm! Thank you!!

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi apelleti 

     

    it works because Y is after N alphabetically.

    this shall also work:

    Column2 =

    VAR _line = TableName[Line]

    VAR SigSeg =

    MAXX(

        FILTER(TableName, TableName[Line] = _line)

        TableName[Significant Segment])

    )

    RETURN

    IF(SigSeg="Yes", "Yes", "No")

    • apelleti's avatar
      apelleti
      Helper I
      3 years ago

      FreemanZ 

       

      That makes sense. I'm really glad you gave this explanation because I need to repeat the function for fields that use different booleans (not just "Yes" and "No").

       

      Thanks again for your help!

    • apelleti's avatar
      apelleti
      Helper I
      3 years ago

      Hi FreemanZ 

       

      How would I do it if, instead of just "Yes" and "No", there were 4 text options representing different ranks of risk, and the text of interest is not alphabetically the min or max? For example, if the "Significant" column contained either "Not present", "Weak", "Moderate" or "Strong" and I wanted to assign all values the "worst" possible outcome present in the line? for this example, the "best" outcome would be "not present" and the "worst" would be "strong". So if one segment in the line includes "strong", all values would be assigned "strong". If "strong" is not present, it would move to "moderate" (if present) and so on. 

       

      Thank you!

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi apelleti 

        1) that could be several nested ifs, comparing one after another.

        2) Or it would also be advisible to encode strong/moderate/not present/worst as 1/2/3/4, by introducing another dimtable with only two columns, one consists strong/moderate/not present/worst and the other 1/2/3/4. Then MINX/MAXX could be used on the 1/2/3/4 column.