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 Power Users,
I'm still learning powerbi. I need help in getting below sql query write in power bi using dax.
select sum(Monthcount), sum(Monthcount)/12 from (select indexid, count(distinct MonthID) as MonthCount, Count(Distinct indexId) as Indexid_count, count(distinct MonthID)/6 as ABC from Table-1
Innerjoin Table-2 on Table-1. MonthID= Table-2.MonthID
Where monthyear between '202001' and '202010'
group by indexid
I need this query to write using DAX.
Thanks
Hi @Kumarcy ,
Would you please try something like:
sum(Monthcount)/12 =
VAR a =
SUMMARIZE (
FILTER (
NATURALINNERJOIN ( Table - 2, Table - 1 ),
'Table-2'[monthyear] >= 202001
&& 'Table-2'[monthyear] <= 202010
),
'Table-2'[indexid],
"MonthCount", DISTINCTCOUNT ( 'Table-2'[MonthID] ),
"ABC", DISTINCTCOUNT ( 'Table-2'[MonthID] ) / 6
)
RETURN
SUMX ( a, [MonthCount] ) / 12
sum(Monthcount) = VAR a =
SUMMARIZE (
FILTER (
NATURALINNERJOIN ( Table - 2, Table - 1 ),
'Table-2'[monthyear] >= 202001
&& 'Table-2'[monthyear] <= 202010
),
'Table-2'[indexid],
"MonthCount", DISTINCTCOUNT ( 'Table-2'[MonthID] ),
"ABC", DISTINCTCOUNT ( 'Table-2'[MonthID] ) / 6
)
RETURN
SUMX ( a, [MonthCount] )
If you need accurate dax formula, please show us some sample data by onedrive for business.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Kumarcy , You can have measures like this
sumx(values('Table-1'[indexid]), distinctcount('Table-1'[indexid]))
sumx(values('Table-1'[indexid]), distinctcount('Table-1'[MonthID]))
sumx(values('Table-1'[indexid]), distinctcount('Table-1'[MonthID]))/12
Both Tables should join on MonthID in power BI and where you can take filter in the slicer
You can refer my Dax vs SQL series, if that can help https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj
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 |
---|---|
113 | |
100 | |
77 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |