Converting SQL Query to a DAX query

[vikramd8]

Hi All, 

please help me to convert this SQL query to a DAX query. 

select b.Name
from [CPHS_Dev_New].[Health].[Patient] a
full outer join [CPHS_Dev_New].[Common].[Vocabulary] b
on a.SmokingStatusCode=b.Code
where b.VocabularyTypeId=20. 

[v-jiascu-msft]

Hi @vikramd8,

Why do you want a DAX query for this SQL statement? You can do it without a code.

1. Import tables Patient and Vocabulary.

2. Establish relationship between Patient[SmokingStatusCode] and Vocabulary[Code].

3. Drag the Vocabulary[Name] to a table visual.

4. Filter Vocabulary[VocabularyTypeId] equal 20 either by a slicer or a filter in the pane.

The Dax could be like this following the step 2 above.

Names =
CALCULATE (
    CONCATENATEX ( VALUES ( Vocabulary[Name] ), [Name], "-" ),
    Vocabulary[VocabularyTypeID] = 20
)

The CONCATENATEX is trying to convert table values to a scale value.

Best Regards!

Dale