topic Using two related columns within sumx causes error in DAX Commands and Tips

Using two related columns within sumx causes error

akhaliq7 — Fri, 05 May 2023 09:51:15 GMT

I have a sumx function that first gets a value

if fact[date1] > related(table2'[date2]), 1, 0

I then use the filter function after sumx to filter by the fact table

Upto here the code runs fine. its when I try to filter by another 3rd table it causes an error

RELATED('table3[column1]) <> "Value"

This causes an error

FULL CODE BELOW:

CALCULATE ( SUMX ( 'FACT TABLE', IF ( [date1] > RELATED('table2'[date2]), 1, 0 ) ), 'FACT TABLE'[Column2] <> "VALUE" RELATED('table3'[column3]) <> "VALUE" )

Re: Using two related columns within sumx causes error

johnt75 — Fri, 05 May 2023 09:58:19 GMT

I don't think you need RELATED for that, you can simply use 'table3'[column3] <> "value"

Re: Using two related columns within sumx causes error

tamerj1 — Fri, 05 May 2023 10:41:31 GMT

Hi akhaliq7

please try

=
COUNTROWS (
FILTER (
'FACT TABLE',
'FACT TABLE'[date1] > RELATED ( 'table2'[date2] )
&& 'FACT TABLE'[Column2] <> "VALUE"
&& RELATED ( 'table3'[column3] ) <> "VALUE"
)
)

Re: Using two related columns within sumx causes error

akhaliq7 — Fri, 05 May 2023 11:18:52 GMT

ok thanks for that never knew if its in the filter function you don't need to use related. the filter function must just automatically detect the relationship. anyways thanks for that