Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |