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.
Hi all,
I need a conversion from SQL to DAX. It seems so simple but I could not figure it out.
What I need is, simply put, the number of email addresses against every line in the given table, here FactSales. I would like to have it as a calculated column rather than a measure.
SELECT EMAIL,COUNT(*) OVER (PARTITION BY EMAIL) NumberOfEmailAddress
FROM FactSales
WHERE EMAIL IS NOT NULL
SalesOrderID | EmailCount | |
1 | a@hotmail.com | 3 |
1 | a@hotmail.com | 3 |
2 | a@hotmail.com | 3 |
3 | b@gmail.com | 2 |
4 | b@gmail.com | 2 |
6 | c@yahoo.com | 4 |
7 | c@yahoo.com | 4 |
7 | c@yahoo.com | 4 |
7 | c@yahoo.com | 4 |
Thanks in advance for any replies
Solved! Go to Solution.
Hi mhakyil, this is classic "simple yet not easy"... because like your SQL PARTITION, the window is needed for context, so add calculated column as:
DAX EmailCount =
CALCULATE(
COUNT('FactSales'[EMAIL]),
ALLEXCEPT('FactSales', 'FactSales'[EMAIL])
)
Hi mhakyil, this is classic "simple yet not easy"... because like your SQL PARTITION, the window is needed for context, so add calculated column as:
DAX EmailCount =
CALCULATE(
COUNT('FactSales'[EMAIL]),
ALLEXCEPT('FactSales', 'FactSales'[EMAIL])
)
I don't read SQL very well, what are you trying to do? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Note, that article specifically calls out, don't just post SQL and ask for a translation to DAX/M...
I thought the code would speak for it self. What I need is, simply put, the number of email addresses against every line in the given table, here FactSales. I would like to have it as a calculated column rather than a measure.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |