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.
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]
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 |
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
@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 |
@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.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |