cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sabeensp Member
Member

Measure based on Sub Query

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Measure based on Sub Query

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.  

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Measure based on Sub Query

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 Member
Member

Re: Measure based on Sub Query

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

Super User III
Super User III

Re: Measure based on Sub Query

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.  

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors