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

     

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.