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!

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi Jilanibasha ,

 

Try:

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

 

 

Best Regards,
Kelly

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

Jilanibasha's avatar
Jilanibasha
Frequent Visitor
5 years ago

Hi Kelly,

The above formulae not accepting IN conditon checking with Variable. it is throwing no of arguments is invalid.

'ContactTopic'[contackey] IN _table

 

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!