Want to create a measure not a column with related values pulled from different tables

[Basmall_B]

Hi 

I am making my transition from sql to DAX and is in the process of learning DAX syntax.

What I would like to do is to create  measure to show concatenated values from two different tables which are related from an Analysis server tabular model. The measure will show as a column in my table visual.  Adding a custom calculated column is not an option as I dont have rights to make changes to the Tabular model. 

I dont need this be done for all rows only for rows that have a data type "WaterQuality" 

Please see the attached sample table and data model

Here is what i am trying to achieve. select from table1 column, table 2 column  concat those columns based on a filter column in table 3 where data category = 'Water Quality'

Thank you for help in advance

 

[CNENFRNL]

Hi, @Basmall_B , based on your description and illustration, I'd obtain filtered columns this way,

SampleNames Filtered = CALCULATE ( VALUES ( DIMSAMPLENAME[SampleName] ), Fact_Readings )

Locations Filtered = CALCULATE ( VALUES ( DimLocation[LOCNAME] ), Fact_Readings )

[Basmall_B · Accepted solution]

Wow thank you very much it works. I am almost close to the final desired output.

This is  what I ended up doing for a Measure

LocationSamples =
(
VAR SampleNames = CALCULATE ( VALUES ( DIMSAMPLENAME[SampleName] ), Fact_Readings )
VAR Locations = CALCULATE ( VALUES ( DimIntervals[INTERVAL_NAME] ), Fact_Readings )
Return
CONCATENATE( Locations, SampleNames)
)

I want to add a space or  comma using concatenate  in between the location and sample names and the visual comes up empty 

when I  nest the concatenate

CONCATENATE(Locations, CONCATENATE(", ", SampleNames))

I even tried "& &" since both the variables are returning strings . The version of powerbi that we have that is compatible with or OnPerm server doesnt have the new COMBINEVALUES function

Locations&" , "&SamplesNames

What am I missing here?