DAX Measure with multiple columns from different related tables

[sonam7]

Hi,

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.

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.

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

[v-lid-msft · Accepted solution]

Hi @sonam7 ,

We can use the following measure to meet your requirement:

InprogressEnvironments = 
CALCULATE (
    DISTINCTCOUNT ( 'vwEnvironments'[EnvironmentID] ),
    FILTER (
        'vwEnvironments',
        OR (
            'vwEnvironments'[Build] IN FILTERS ( Builds[Build] ),
            RELATED ( vwDynamic[TARGETBUILD] ) IN FILTERS ( Builds[Build] )
        )
    ),
    'vwEnvironments'[State] = "In Progress"
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sonam7]

Hi @v-lid-msft  ,

Thanks a lot for your help with the DAX Measure! However, when I run using this formula in my .pbix I get the following error:

The column 'vwDynamic[TARGETBUILD]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Your .pbix is very similar to mine.

I can see that in my .pbix, tables are connected to each other using the below shown relationships.

Build ---> vwEnvironments (one to Many) ON (Builds.build = vwEnvironments.Build)

vwEnvironments <--> Dynamic (Many to One) ON (vwEnvironments.RunID = Dynamic.RunID)

Anyidea why am I getting this error and how can I fix this issue? Thanks!

[v-lid-msft]

Hi @sonam7 ,

I add the new column runid and create new relation, but it works fine on my side, maybe your targetbuild name is different from mine?

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.