Forum Discussion

vikramd8's avatar
vikramd8
Frequent Visitor
8 years ago

Converting SQL Query to a DAX query

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. 

 

 

Need Help

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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