Sub queries in PowerBi

[Anonymous]

Hi, I am having one SQL query and trying to convert it to DAX. I am newbie to DAX. please help

SELECT count(*) from  REQ_RELEASES WHERE  REQ_RELEASES.RQRL_REQ_ID IN(
SELECT DISTINCT RC_REQ_ID FROM  REQ_COVER WHERE  REQ_COVER.RC_ENTITY_ID IN(
SELECT   DISTINCT( TESTCYCL.TC_TEST_ID) FROM  TESTCYCL
WHERE  TESTCYCL.TC_ASSIGN_RCYC= 1311   ))  and RQRL_RELEASE_ID =1067

In this query, TC_ASSIGN_RCYC and RQRL_RELEASE_ID are input from slicer selection.

Any help would really appreciate. 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could refer to the following measure:

SQL_TO_DAX =
VAR a =
    CALCULATETABLE (
        DISTINCT ( TESTCYCL[TC_TEST_ID] ),
        FILTER (
            TESTCYCL,
            TESTCYCL[TC_ASSIGN_RCYC] = SELECTEDVALUE ( TESTCYCL[TC_ASSIGN_RCYC] )
        )
    )
VAR b =
    CALCULATETABLE (
        DISTINCT ( REQ_COVER[RC_REQ_ID] ),
        FILTER ( REQ_COVER, REQ_COVER[RC_ENTITY_ID] IN a )
    )
RETURN
    COUNTROWS (
        FILTER (
            REQ_RELEASES,
            REQ_RELEASES[RQRL_REQ_ID] IN b
                && REQ_RELEASES[RQRL_RELEASE_ID ]
                    = SELECTEDVALUE ( REQ_RELEASES[RQRL_RELEASE_ID ] )
        )
    )

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[lbendlin]

Generally you do that during the Power Query transformations, with grouping and filtering operations.  If you have to do it in DAX you will want to delegate as much work as possible to the data model, and then use VALUES() or DISTINCT() profusely to get your distinct values.