Home Forums Power Pivot Return unique claim numbers in date range

This topic contains 9 replies, has 2 voices, and was last updated by  brawnystaff 5 months, 1 week ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #9740

    brawnystaff
    Participant
    • Started: 6
    • Replies: 17
    • Total: 23

    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.
    #9742

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    BrawnyStaff,

    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?

    #9743

    brawnystaff
    Participant
    • Started: 6
    • Replies: 17
    • Total: 23

    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.

    #9744

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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.
    #9746

    brawnystaff
    Participant
    • Started: 6
    • Replies: 17
    • Total: 23

    Yes, followed your instructions and got there..

    #9747

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    BrawnyStaff,

    …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:

    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], [ICN] DESC

    #9748

    brawnystaff
    Participant
    • Started: 6
    • Replies: 17
    • Total: 23

    Wow..never new this existed.  But now see how you created it.  Thanks..

    #9749

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    You’re welcome!

    #9750

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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?

    #9751

    brawnystaff
    Participant
    • Started: 6
    • Replies: 17
    • Total: 23

    No, I have not ventured in Power BI yet.  Plan to do so in the future..Thanks again.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.