Search code examples
powerbisubquerydaxmeasure

Convert SQL subquery to Power BI DAX

As a newbie of PowerBI, I was so struggled with this problem. I want to create a custom measure, and it can be solved easily with SQL statement which looks like this:

select count(*) from Task
 where case_num in(
    select case_num from Task
     where task_type="DDD") sub

Can somebody help me with this. Thank you!!!!!

The table is roughly like this

Solution

  • You can isolate the case_nums that have a specific task type

    VAR tbl = CALCULATETABLE(VALUES('Table'[case_num]),'Table'[task_type] = "DDD")

    And then apply that table as a filter:

    Measure = 
VAR tbl = CALCULATETABLE(VALUES('Table'[case_num]),'Table'[task_type] = "DDD")
RETURN CALCULATE(COUNT('Table'[case_num]), TREATAS(tbl,'Table'[case_num]))

    or

    Measure = 
VAR tbl = CALCULATETABLE(VALUES('Table'[case_num]),'Table'[task_type] = "DDD")
RETURN CALCULATE(COUNT('Table'[case_num]), 'Table'[case_num] IN tbl)