Forum Discussion

Jilanibasha's avatar
Jilanibasha
Frequent Visitor
5 years ago
Solved

Convert SQL to DAX

Hi All,   I need a requirement of implement below sql query in DAX.    select count(*) from ContactTopic where contactkey in ( select distinct contactkey from ContactTopic where topic='local off...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Jilanibasha ,

     

    Modify the measure as below:

    Measure =
VAR _table =
    CALCULATETABLE (
        VALUES ( 'ContactTopic'[ContactKey] ),
        FILTER ( ALL ( 'ContactTopic' ), 'ContactTopic'[topic] = "local office" )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'ContactTopic' ),
        FILTER (
            ALL ( 'ContactTopic' ),
            'ContactTopic'[ContactKey]
                IN _table
                && 'ContactTopic'[topic] <> "local office"
        )
    )

    Check my sample .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

themistoklis's avatar
themistoklis
Icon for Community Champion rankCommunity Champion
5 years ago

Hello Jilanibasha ,

 

I think you can create 2 tables in Power Query.

 

First Table Using the SQL Query (Table 1): 

select distinct contactkey from ContactTopic where topic='local office'

 

Second Table Using SQL Query to Bring ALL DATA (Table 2):

select * from ContactTopic

 

In Data model join the 2 tables on contactkey (one to many relationship - Cross Filter to Both).

 

Then write the following dax formula and see if it works

 

 

 

 

NumofRecords =
VAR __records =
    CALCULATETABLE ( VALUES ( Table2[Contactkey] ), Table1 )
RETURN
    SUMX ( __records, CALCULATE ( COUNTROWS( Table2 ), KEEPFILTERS(Table2[topic]<>"local office") ))