Convert SQL to DAX

[Jilanibasha]

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

[v-kelly-msft]

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]

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 · Accepted solution]

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]

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") ))

[Pragati11]

Hi @Jilanibasha ,

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

Thanks,

Pragati

[Jilanibasha]

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]

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-visua...
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