topic Re: DAX expression question in DAX Commands and Tips

DAX expression question

apelleti — Mon, 12 Dec 2022 22:45:58 GMT

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

Re: DAX expression question

FreemanZ — Tue, 13 Dec 2022 00:02:11 GMT

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")

Re: DAX expression question

FreemanZ — Tue, 13 Dec 2022 02:37:16 GMT

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:

Re: DAX expression question

tamerj1 — Tue, 13 Dec 2022 03:01:30 GMT

Hi apelleti

you can also use

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

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

Re: DAX expression question

apelleti — Tue, 13 Dec 2022 14:49:53 GMT

FreemanZ like a charm! Thank you!!

Re: DAX expression question

FreemanZ — Tue, 13 Dec 2022 14:56:48 GMT

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")

Re: DAX expression question

apelleti — Tue, 13 Dec 2022 15:36:28 GMT

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!

Re: DAX expression question

apelleti — Wed, 14 Dec 2022 16:35:06 GMT

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!

Re: DAX expression question

FreemanZ — Thu, 15 Dec 2022 09:01:54 GMT

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.