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!

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

 

 

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi Jilanibasha ,

     

    Always try to share sample data as an attachement. The screen-shot of data doesn't help me if I want to get it in Power BI at my end. 🙂

     

    If I understand your scenario:

    1. First thing you need is interaction between your two tree-map visuals. This can be easily achieved by in Power BI by using Edit Interaction. Also in Power BI, by default the interactions are enabled between the visuals. So if you select any area on your tree-map visaul on the left chart, the right one will get filtered for the selection made on the left chart. If this is not enabled by default, For details check this link: https://www.wisdomaxis.com/technology/software/powerbi/interview-questions/what-is-the-default-visual-interaction-in-power-BI-how-to-change-it-how-many-types-of-interactions-are-there.php
    2. I don't understand the second part of your requirement mentioned as follows: "the selected topic should excluded and contactkeys should be in selected topic for first visual"

    Thanks,

    Pragati