Need support for How to handle sub query in Measure / DAX functions.

[Anonymous]

Hi All,

This is my SQL query,

SELECT count(*) as success_count FROM (SELECT `server_name` FROM `windows_linux_patch` where `status`='Successful' AND `server_name` NOT IN(SELECT `server_name` FROM `windows_linux_patch` WHERE `status` = 'Failed' group by `server_name` ) group by `server_name`) AS success_count ;

I have tried to convert up to below, But I am struggling an how to apply subquery in the below conditions.

Success_Counts =
CALCULATE (
DISTINCTCOUNT ( windows_linux_patch[server_name] ),
FILTER (
windows_linux_patch,
windows_linux_patch[status] = "Successful" 
),
GROUPBY(
windows_linux_patch,
windows_linux_patch[server_name])
)

Kindly give your support. I am waiting for your updates.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

SELECT count(*) FROM table_name WHERE field_name = 'abc'

equal to

CALCUALTE( count('table_name'), FILTER( 'table_name', field_name = 'abc'))

Maybe some differents depend on sepecial situation. In your case try this:

Success_count =
VAR _Failed =
    CALCULATETABLE (
        VALUES ( 'windows_linux_patch'[server_name] ),
        FILTER ( 'windows_linux_patch', [status] = 'Failed' )
    )
VAR _Succ =
    CALCULATETABLE (
        VALUES ( 'windows_linux_patch'[server_name] ),
        FILTER (
            'windows_linux_patch',
            [status] = 'Successful'
                && NOT ( [server_name] IN _Failed )
        )
    )
RETURN
    COUNTROWS ( _Succ )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your support and your response.

[amitchandak]

@Anonymous , try a measure like

Countx(filter(summarize(windows_linux_patch, windows_linux_patch[server_name] , "_1" , countrows(filter(windows_linux_patch, windows_linux_patch[status] = "Successful" )) , "_2" , countrows(filter(windows_linux_patch, windows_linux_patch[status] = "Failed" ))), Not(isblank(_1)) && isblank(_2)))

[Anonymous]

Thanks for your support and your response.