cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sabeensp Helper IV
Helper IV

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
Highlighted
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 Helper IV
Helper IV

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors