Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sub queries in PowerBi

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. 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

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

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

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