Forum Discussion
DAX Help
Please I need help converting the SQL Code below to Power BI DAX. a and b aliases are for different tables respectively. Thank you.
(((COUNT(a.[Case ID])*365/MAX(b.[Days in Month])))/(MAX(b.Membership)/1000))
/
CASE WHEN Month(GetDate())=1 THEN 12
ELSE Month(DateAdd(mm, -1, Getdate()))
END AS [Admits/K]
,(SUM(a.SaleDays)*365/MAX(b.[Days in Month]))/(MAX(b.Membership/1000))
/
CASE WHEN Month(GetDate())=1 THEN 12
ELSE Month(DateAdd(mm, -1, Getdate()))
END AS [Days/K]
5 Replies
Iyke4joe - Honestly, this is generally not the best way to go about this. It's far easier to start with source data example, expected output instead of trying to parse SQL code. That said, equivalent of CASE statement is SWITCH.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Greg, thank you for your response. For the sake of security, I can't give the exact data but here's a typical representation of it.
Thank you in anticipation of your response.
Table A
CaseID
MemberID
SaleDays
Date
234
cc1
4
3/6/2019
456
cc2
3
4/12/2019
789
cc3
9
5/19/2019
Table B
Member_id
Membership
Year
Month
Days in Month
cc1
2
2019
3
3
cc2
3
2019
4
30
cc3
5
2019
5
31
cc4
9
2019
7
31
cc5
7
2019
8
31
Iyke4joe - Yep, understood, sample data is great. And what is the expected output from the sample data you provided? Once I understand that, should be able to mock this up in a PBIX for you.
Here's the explanation I got for the code. Will this help?
Metric Membership Days in Year Days in Month Formula Comment Solution/ K 315 12821 365 31 =(A2*C2/D2)/(B2/1000) (metric * Days in Year/Days in Month)/(Membership/1000) ; Goes down to days and helps in Feb since it has fewer days 289.28
Hey Greg, thanks for your response and I apologize for just getting back to you on this.
I have asked and could not get any right explanation for the logic behind the SQL code. I am just responsible to create report and dashboard on power BI using the Code provided by the DBA team. I ahve played with this in different ways to avoid using the end column but still end up needing to use it. If there's anyway you can help, I will appreciate this. I don't know if they are doing it per every 1000 membership( just a hunch). Pls help as this is the only thing holding me back from moving forward.
Thank you
