Measure based on Sub Query

[sabeensp]

Hello,

How do I craete a measure based on a SQL Sub query (below)

Select distinct COUNT(Column1)
from TABLE1
where COLUMN2 = 'C'
and not exists (
select Column 4, Column 5
from  TABLE2
where ltrim(rtrim(columnX)) <> 'RED'
and COLUMNB = "LARGE"
and COLUMNY = 20)

[d_gosbell]

This question is not clear in it's current form. Try re-posting your question using the following guidelines:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

[sabeensp]

@d_gosbell 

What I'm looking for my measure to return COUNT.

I have 2 tables in my model Table1 and Table2

Both table have  a relationship 1:M.

Query below is the SQL query, which I need to migrate to Power BI. How do I do that, what DAX I need to write to accomplish sam eresult as the SQL query below?

Select distinct COUNT(Column1)
from TABLE1
where COLUMN2 = 'C'
and not exists (
select Column 4, Column 5
from TABLE2
where ltrim(rtrim(columnX)) <> 'RED'
and COLUMNB = "LARGE"
and COLUMNY = 20)

[Cmcmahan · Accepted solution]

You're swapping between Column1-5 and ColumnB-Y in your SQL. I can't even tell what the foreign key linking the two is. 

It seems you're trying to count the primary key from table 1, where the related values in table2 are not red, large, and 20? If you could share a subset of sample data you have in PowerBI in a copy/pasteable form, this would be much clearer to us.

I'm guessing your DAX measure would look like this, but without a more complete picture, it's a complete guess.

Count Things = 
CALCULATE(
    DISTINCTCOUNT(Table2[ForeignKey]),
    RELATED(Table1[Column2]) = "C" && Table2[ColumnX] <> "Red" && Table2[ColumnB] = "Large" && Table2[ColumnY] = 20
)

This seems.... like a very specific measure, and not in a good way.  I would consider setting up a simpler measure like DISTINCTCOUNT(Table2[ForeignKey]), and setting up slicers that filter on the other columns.  You could display the simpler measure in a card, and even have the slicers only affect the one card visual if you wanted. This way, it's responsive and you're not locked in to the one combination of C, not Red, Large, and 20.