Converting SQL CASE Statement Into a DAX Measure

Anything related to PowerPivot and DAX Formuale
rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Tue May 21, 2019 11:38 pm

I am in need of a nudge in the right direction to solve my DAX riddle. I would like to convert the following SQL Statement into a DAX Measure I am focused on the SQL CASE Statement of the Query. Depending on the evaluation of the case statement it will either add the chgamt (Charge Amount from the Charge Table), subtract it, or set it to "0". This all dependent on the postingdate column, and the backoutdate column. A calculated column will not work because the dates in the case statement should not be hard coded, yet they are dynamic in the report and fed through user activity. In converting this report over to power pivot I used disconnected tables/ parameters to for the dates.

To Further Explain, posting date is when the charge was posted for the service provided. backoutdate is if the charge was corrected, or removed from the patients account. (A refund was provided for example). The ability to have the dates dynamic is because I want to see the total charges in a time range, So if select the dates March 1 2018 through March 31 2018 I want to see the sum of all charges for the Timeframe for all accounts. Then if I decide to Change my Date range from March 1 2018. April 30th 2018, and if one of the charges was corrected and had a backoutdate within that time frame, I subtract the charge from the total instead of adding it. So basically the same charge can be added or subtracted (included, or not included) in the total depending on the date range. This builds a great revenue perspective.

Basically I am really not sure where to go from here. My problem is, how do I add the logic for the measure to add, subtract etc. - If Statements? As of now I only plan on slicing this data by provider (Provider Code Number in the pivot), I will be more than happy to share data. This has been a curse for me, so any help will be very very appreciated! (There are also attachments to this post)

SELECT provcode,
SUM(case when c.correction = 'N'then c.chgamt
else case when postingdate < '20180301' then -chgamt
else case when backoutdate > '20180331' then chgamt
FROM dbo.charge_t as c
WHERE c.revctr NOT IN (3,7,12,13,14,15,16,17)
AND c.provcode = '138'
AND (c.postingdate BETWEEN '20180301' AND '20180331'
OR c.backoutdate BETWEEN '20180301' AND '20180331')
GROUP BY provcode;
Attachments
DAX-Help.zip
(253.72 KiB) Downloaded 25 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Converting SQL CASE Statement Into a DAX Measure

Postby PhilC » Wed May 22, 2019 8:44 am

Have a look at the SWITCH function

https://powerpivotpro.com/2012/06/dax-m ... or-switch/

Cheers
Phil

rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Re: Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Thu May 23, 2019 1:18 am

Switch will not work with this. This will have to be a measure and not a calculated column in this regard. The summation is dynamic in nature. The Dates are selected (i.e from a slicer in this case) and the measure will be calculated. The hardcoded dates in the CASE statement are just for the example. The dollar amount in the charg_t[chgamt] column will be added, or subtracted based on the date comparison.

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Converting SQL CASE Statement Into a DAX Measure

Postby PhilC » Thu May 23, 2019 12:50 pm

SWITCH is not just for calculated column, so do not discount it on that basis alone.

Perhaps post a workbook with data and let's see what might work. It is likely you will need to use SUMX to ensure the measure is applying the calc at the row level, and will need to consider if the slicer selection is not a single date.

Cheers
Phil

rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Re: Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Fri May 24, 2019 1:37 am

Thanks for the help Phil, I get what you are saying but I am not really sure how I can Use SWITCH() as a measure (in this case) because I need single values for the dates on the left side of the inequalities, and they can't be "naked" columns. I'm sure there is a way to do it, but I'm not super versed in the DAX Language enough i guess to figured out the logic tricks I need.

Thanks though!

Ricky

rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Re: Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Fri May 24, 2019 4:46 am

I'm Kind of there, but not , This somewhat worked on my POC model, but It's filtering over the Charges table (Fact table) and I need to switch it over to the date dimension.

=
SUMX (
FILTER (
Charges,
Charges[postingdate] >= [Start Date] -- Measure from Slicer Table
&& (
[End Date] < Charges[backoutdate]
|| ISBLANK ( [backoutdate] -- Measure from Slicer Table )
)
),
Charges[amount] -- it is now only adding....This crushes my CPU when running on a large table.
)

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Converting SQL CASE Statement Into a DAX Measure

Postby PhilC » Fri May 24, 2019 8:49 am

It really is easier if you provide a workbook with the model in order for help to be provided (well, at least for me).


Cheers
Phil

rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Re: Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Fri May 31, 2019 10:53 pm

Sounds good Phil, Attached is a mock up of what I am doing. Please feel free to reach out to me! My email is rick314@outlook.com

Thanks so much!

-Ricky
Attachments
Dates Work In Progress.xlsx
(373.1 KiB) Downloaded 23 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Converting SQL CASE Statement Into a DAX Measure

Postby PhilC » Sun Jun 02, 2019 2:05 pm

Hi Ricky, thanks for posting.

While not positive there is not a better approach that does not use SWITCH, and I have not validated this:

=SUMX(Charges,
SWITCH(TRUE(),
Charges[correction]="N",Charges[amount],
Charges[postingdate]<[Start Date],-Charges[amount],
Charges[backoutdate]>[End Date],Charges[amount],
0))

Your SQL statement has additional filters in the WHERE clause, but those fields are not in the table. The approach may change in they need to be factored in as well.

Cheers
Phil
Attachments
Dates Work In Progress SWITCH PPF.xlsx
(386.91 KiB) Downloaded 21 times

rbee
Posts: 6
Joined: Tue May 21, 2019 10:29 pm

Re: Converting SQL CASE Statement Into a DAX Measure

Postby rbee » Tue Jun 04, 2019 5:54 am

Thanks Phil for taking a look at this! I really appreciate it. Sharing my problem has really helped me a lot!

Take Care,

Ricky


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron