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 office')
and topic<>'local office'
 
It has 2 conditions, one is filterout the contactkey for ony 'local office' and apply 2nd filter is local office topic sould to be there in first query result.
Some one please help me to write DAX code.
 
Thanks in advance
Jilanibasha
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!

7 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi Jilanibasha ,

     

    Can you share more details like sample data and expected output, rather than just sql query?

     

    Thanks,

    Pragati

    • Jilanibasha's avatar
      Jilanibasha
      Frequent Visitor
      5 years ago

      Hi Pragati11 ,

       I have 2 tree maps visuals, if i select on first visual, second visual should get filter, the selected topic should excluded and contactkeys should be in selected topic for first visual. the query woking fine in SQL. need to convert same logic in dax.

       

       

      and the data is

       

      Thanks for help in advance.

       

       

      Thanks,

      Jilani

       

       

  • themistoklis's avatar
    themistoklis
    Community 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") ))

     

     

     

     

     

  • 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!