DAX Measure with multiple columns from different related tables in filter expression

Question

I need help with DAX Measure. I have 3 tables Builds, Dynamic, Environments. They are all related with each other. I have created a report with Builds slicer from Builds table and a table chart with the following columns:

BUILD[Environments Table]
Target Build[Dynamic Table]
State[Environements Table]
EnvironmentID[Environments Table]

Now I want to create a measure to calculate count of In Progress Environments where Build = selected build in slicer or target build = selected build in slicer.

Can someone please help me with the correct formula. Thanks!

I came up with the below formula:

InprogressEnvironments = 
VAR SelectedSlicerValue = SELECTEDVALUE(Builds[Build])
RETURN
  VAR CountEnvs =
    CALCULATE (
      DISTINCTCOUNT ( 'vwEnvironments'[EnvironmentID] ),
      OR (
        'vwEnvironments'[Build] = SelectedSlicerValue,
        vwDynamic[TARGETBUILD] = SelectedSlicerValue
      ),
      'vwEnvironments'[State] = "In Progress"
    )
  RETURN
    CountEnvs

However, this measure is returning the following error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

The error is based on your OR function. Simple predicates to CALCULATE can only refer to a single column. Would you please provide a sample of your data and model, as well as expected output based on that sample? It's impossible for us to infer these things. Please see this page on producing a minimal working example. — greggyb

StelioK StelioK · Accepted Answer · 2019-09-19T17:23:01

I am not sure what the relationship between the two table is but something like this may work:

InprogressEnvironments =
VAR SelectedSlicerValue =
    SELECTEDVALUE ( Builds[Build] )
VAR CountEnvs =
    CALCULATE (
        DISTINCTCOUNT ( 'vwEnvironments'[EnvironmentID] ),
        'vwEnvironments'[State] = "In Progress",
        'vwEnvironments'[Build] = SelectedSlicerValue
            || RELATED ( 'vwDynamic'[TARGETBUILD] ) = SelectedSlicerValue
    )
RETURN
    CountEnvs

DAX Measure with multiple columns from different related tables in filter expression

1 Answers