DAX expression question

[apelleti]

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 for that line is assigned "Yes" in the column "Significant line". 

I tried the formula below which works as a measure, but does not work directly in the table (i.e when clicking "new column" in the table view).

SignificantLine = MAXX(FILTER(ALL('Table'),'Table'[Line]=SELECTEDVALUE('Table'[Line])),'Table'[SignificantSegment)

Thanks in advance!

Line	Segment	Significant Segment	Significant Line
1	1	Yes	Yes
1	2	No	Yes
1	3	No	Yes
1	4	No	Yes
2	5	Yes	Yes
2	6	Yes	Yes
2	7	Yes	Yes
2	8	Yes	Yes
3	9	No	No
3	10	No	No
4	11	Yes	Yes
4	12	No	Yes
4	13	Yes	Yes

[tamerj1]

Hi @apelleti 

you can also use

SignificantLine =
IF (
"Yes"
IN CALCULATETABLE (
VALUES ( 'Table'[SignificantSegment] ),
ALLEXCEPT ( 'Table', 'Table'[Line] )
),
"Yes",
"No"
)

or

SignificantLine =
IF (
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Table'[SignificantSegment] ),
ALLEXCEPT ( 'Table', 'Table'[Line] )
)
) > 1,
"Yes",
"No"
)

[FreemanZ]

hi @apelleti 

For calculated column, try like:

Significant Line =

VAR _line = [Line]

VAR SigSeg = 

CALCULATE(

     MAX([Significant Segment]),

     [Line] = _line

)

RETURN

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

[FreemanZ · Accepted solution]

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]

@FreemanZ like a charm! Thank you!!

[FreemanZ]

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]

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]

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.

[apelleti]

@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!