May 13, 2018 at 11:34 pm #9740
I am using Excel 2016 and PowerPivot to return unique claim numbers that occurred in one table (Part B) for each patient/date of service that fall within a date range for same patient in another table (Part A). Currently using a Calculated Column and ConcatenateX to have all claims on one line per patient. However, looking to have it natively unpivoted to that result only shows one claim number per line.
Not sure if this can be done as a Calculated Column or need to convert to a measure. See attached sample. Sheet “Current Layout” shows the current setup (var Matched Part B ICNs is calculated column) and sheet “Desired layout” shows how I would like it to display. Thanks.
Attachments:You must be logged in to view attached files.May 14, 2018 at 3:17 am #9742
Since you want the data “natively unpivoted”, a pivot table is not the right path to reach what you described.
You could either create a DAX query inside of an Excel spreadsheet or use Power Query. Which way would you like to go?May 14, 2018 at 3:44 am #9743
EDIT: Actually, have been researching DAX query methods in Excel using “Connections” (assume that is what you mean) and believe Power Query (Get and Transform) may be more in line with what I am use to…Sorry..Thx.May 14, 2018 at 4:03 am #9744
If you go to the DAX Query tab in the attached workbook, then if you right click on one of the cells inside the table, and choose Table… from the popup menu, then choose Edit DAX from the dropdown menu, you will see the formula below, which is a DAX query (returns a table). If this will work for you, I will explain.
EVALUATE SUMMARIZECOLUMNS ( PartA[Date From], PartA[Date Through], PartA[HICN], PartA[Name], PartB[ICN], FILTER ( CROSSJOIN ( PartA, PartB ), PartA[Date From] <= PartB[Date From] && PartA[Date Through] >= PartB[Date From] && PartA[HICN] = PartB[HICN] ) ) ORDER BY [Date From], [Date Through], [Name]
Attachments:You must be logged in to view attached files.May 14, 2018 at 4:17 am #9746
Yes, followed your instructions and got there..May 14, 2018 at 4:56 am #9747
…the resulting DAX query should fit your desired layout.
DAX queries in an Excel worksheet are interesting, once you can get past some of their “special” behaviors.
Special Behavior #1:
To create custom DAX query, you have to overwrite a system created DAX query.
On the Data tab of the Excel ribbon, select Existing Connections, then when the Existing Connections dialog launches, select the Tables tab and then choose one of the tables under “This Workbook Data Model” and then click on the Open button. At this point, the Import Data dialog launches and, after a visual confirmation that the “Table” option is selected, choose OK and the system will create a DAX query for you (which you will overwrite).
Special Behavior #2
If you selected a table that has one or more column names similar to your eventual custom query, the system will create a column order that you may not be able to override, regardless of what column order your custom query has. To this end, I created the NoData table and added it to the Power Pivot data model and, when working with Special Behavior #1 above, I chose the NoData table, instead of TableA or TableB.
So now we have worked past the special behaviors, and you can then select one of the cells in the table created in #2 above, right click and choose Table… from the popup menu, then choose Edit DAX from the dropdown menu, you will see a drop down for Command Type, change this from Table to DAX and enter a table formula that will always start with EVALUATE.
If there are no syntax errors in your DAX expression, and the DAX expression will return a table (not a scalar value), you can click the OK button and you will return to the worksheet and see the results of your DAX query.
In the DAX expression that I created, the CROSSJOIN in the FILTER expression joins all of the rows in TableB to each row of TableA, and the remainder of the FILTER expression keeps only the records where Names match and the Date From of TableB is within the date range of Table A.
If ICN numbers have to follow in descending order, as shown in the desired layout, you could tweak the original formula to:
CROSSJOIN ( PartA, PartB ),
PartA[Date From] < = PartB[Date From] && PartA[Date Through] >= PartB[Date From]
&& PartA[HICN] = PartB[HICN]
ORDER BY [Date From], [Date Through], [Name], [ICN] DESCMay 14, 2018 at 5:00 am #9748
Wow..never new this existed. But now see how you created it. Thanks..May 14, 2018 at 5:02 am #9749
You’re welcome!May 14, 2018 at 5:12 am #9750
In Power BI Desktop, the process has become much simpler. You just choose the New Table option on the Modeling tab in either the Report or Data view, and enter the table expression (without EVALUATE and without the ORDER BY clause).
You do not need to overwrite a system generated table, and you do not need to worry about column order. Maybe in “Excel 2019” (or whatever the next release will be called) the process will also be simpler.
Have you looked into Power BI?May 14, 2018 at 12:10 pm #9751
No, I have not ventured in Power BI yet. Plan to do so in the future..Thanks again.
You must be logged in to reply to this topic.