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

     

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