Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
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
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |