locked
unique combinations in a table RRS feed

  • Question

  • I have a table. It has four column. Claims ID, Work Order ID (multiple work orders for each claim) pricing type (for each work order) and date of work order. 

    What I want to do is return just the UNIQUE combinations of work order pricing type, BY DATE. So, B/A is different than A/B, and so on. I'm not sure how to do this in power pivot. I've included a screen shot and the file link. Thanks!!   https://www.dropbox.com/s/yjj845kcaust1ro/Book2.xlsx?dl=0

    Oh, and this is a sample data obviously. My data set is much, much larger, thus the need to find unique combinations. 


    • Edited by AlexMartini Tuesday, December 13, 2016 11:07 PM
    Tuesday, December 13, 2016 11:02 PM

Answers

  • Hi AlexMartini,

    First you should add a rank column  ordered by date using the formula below.

    =RANKX(FILTER(Table1,Table1[Claim ID]=EARLIER(Table1[Claim ID])),Table1[Date],,ASC,Dense)



    Then you can get the UNIQUE combinations of work order pricing type, BY DATE as follows.

    1. Click the add-in, open the DAX studio connect the table, type the DAX and generate the UNIQUE combinations filtered by order, please review the result.

    EVALUATE 
    SELECTCOLUMNS(FILTER(Table1,Table1[Rank]=1),"First",Table1[Pricing Type])





    2. Output the result to excel, please see the following screenshot. You will use the similar method to get other UNIQUE combinations.




    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, December 14, 2016 7:22 AM
  • Hi AlexMartini,
    >> I tried that but it says Failed to resolve name SelectColumns. It is not a valid table or function name.

    The SelectColumns is used to create a new table, where do you run the DAX, could you please post a screenshot for further analysis?

    >> In my file, you can see the unique outputs for each claim across the row (starting in row 7) The output above does not match any unique combination.

    Based on my understanding, the first unique combination come from the earliest time type for every Claim ID as followings.

      


    >> if this were the right unique output, would I have to repeat this code 1,000 times, assuming there are 1,000 unique outputs?

    There are no special regular for the unique combination. So this case, I only can filter the Claim ID one by one. If you have several outputs, you can use this solution.  However, if you have multiple unique outputs, you’d better create a pivot table, can search box(highlighted in red line), select what you want. 


     
    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 7:55 AM
  • Hi AlexMartini,

    Try to use the following formula to return the distinct values in special column.

    EVALUATE 
    DISTINCT(SELECTCOLUMNS(FILTER(Table1,Table1[Rank]=1),"First",Table1[Pricing Type]))

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 29, 2016 10:04 AM
  • Hi AlexMartini,

    I run the statement and it works fine. While  the order of combination change, I will post the update here for this issue.


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 30, 2016 9:56 AM

All replies

  • Hi AlexMartini,

    First you should add a rank column  ordered by date using the formula below.

    =RANKX(FILTER(Table1,Table1[Claim ID]=EARLIER(Table1[Claim ID])),Table1[Date],,ASC,Dense)



    Then you can get the UNIQUE combinations of work order pricing type, BY DATE as follows.

    1. Click the add-in, open the DAX studio connect the table, type the DAX and generate the UNIQUE combinations filtered by order, please review the result.

    EVALUATE 
    SELECTCOLUMNS(FILTER(Table1,Table1[Rank]=1),"First",Table1[Pricing Type])





    2. Output the result to excel, please see the following screenshot. You will use the similar method to get other UNIQUE combinations.




    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, December 14, 2016 7:22 AM
  • Hi Angelia. I tried that but it says Failed to resolve name SelectColumns. It is not a valid table or function name. 

    Also, I don't understand the output. In my file, you can see the unique outputs for each claim across the row (starting in row 7) The output above does not match any unique combination. 

    And finally :) if this were the right unique output, would I have to repeat this code 1,000 times, assuming there are 1,000 unique outputs? 

    Wednesday, December 14, 2016 8:00 PM
  • Hi AlexMartini,
    >> I tried that but it says Failed to resolve name SelectColumns. It is not a valid table or function name.

    The SelectColumns is used to create a new table, where do you run the DAX, could you please post a screenshot for further analysis?

    >> In my file, you can see the unique outputs for each claim across the row (starting in row 7) The output above does not match any unique combination.

    Based on my understanding, the first unique combination come from the earliest time type for every Claim ID as followings.

      


    >> if this were the right unique output, would I have to repeat this code 1,000 times, assuming there are 1,000 unique outputs?

    There are no special regular for the unique combination. So this case, I only can filter the Claim ID one by one. If you have several outputs, you can use this solution.  However, if you have multiple unique outputs, you’d better create a pivot table, can search box(highlighted in red line), select what you want. 


     
    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 7:55 AM
  • Hello. Here is the screenshot. 

    Wednesday, December 28, 2016 12:08 AM
  • And no a unique combination is just a work order pattern. 

    Claim ID 1: A, B, B

    if Claim ID 2 has the same combination (by date) A, B, B, then I don't want it returned twice. 

    I just want to see the unique combinations. 

    It could be A....A,A.....B,A,C.....C,D,D,D,D and so on. 

    Wednesday, December 28, 2016 12:10 AM
  • Hi AlexMartini,

    Try to use the following formula to return the distinct values in special column.

    EVALUATE 
    DISTINCT(SELECTCOLUMNS(FILTER(Table1,Table1[Rank]=1),"First",Table1[Pricing Type]))

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 29, 2016 10:04 AM
  • Hi Angelia. That still does not work. It is not recognizing select columns. It is is not a valid table or a function name per the message. 
    • Edited by AlexMartini Thursday, December 29, 2016 6:27 PM
    Thursday, December 29, 2016 6:24 PM
  • Hi AlexMartini,

    I run the statement and it works fine. While  the order of combination change, I will post the update here for this issue.


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 30, 2016 9:56 AM