Forum Discussion

Kumshan450's avatar
Kumshan450
Helper III
8 years ago
Solved

Create new table by filtering column value

Hi,
  
I have created a calculated column which will count rows that are duplicate.
 
Count = CALCULATE(countrows(Receiptdump),ALLEXCEPT(Receiptdump,Receiptdump[RECEIPT_NO]))
 
Receipt noBPIS NOBPIS DateMapping status Count
C00935218006621013x30-01-2018MAPPED3
C00935218006621013x30-01-2018MAPPED3
C00935218006621013x30-01-2018MAPPED3
C009352180066231014x29-01-2018MAPPED2
C009352180066231014x29-01-2018MAPPED2
C009352180066251015x28-01-2018MAPPED1
C009352180066261016x28-01-2018MAPPED1
 
After a lot of googling, I have found that we cannot delete any records in power pivot. 
 
So I wanted to create a new table where Count =1. This will throw me distinct values of receipt no.
 
The objective is to create a relationship with source table by receipt no. 
 
Receipt noBPIS NOBPIS DateMapping status Count
C009352180066251015x28-01-2018MAPPED1
C009352180066261016x28-01-2018MAPPED1
Need Help
  • Kumshan450's avatar
    Kumshan450
    8 years ago

    Hi I Finally got the answer, 

     

    I created the new table by applying the formula using calculate table function. 

     

    Distinctreceiptdump = CALCULATETABLE(Receiptdump,filter(Receiptdump,Receiptdump[Count]=1))

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Kumshan450,

     

    If what you want to acheive is a table which contains a set of all the distinct receipt numbers you can do the following

     

    Power Pivot

     

     

    Power BI

     

    If implementing this in Power BI this is slightly easier. You can follow the steps above replacing Step 1 with creating a calculated table and inputting the measure.

     

    Hope this helps.

     

    Kris

    • Kumshan450's avatar
      Kumshan450
      Helper III
      8 years ago

      Hi, thanks for the reply.

       

      Hi, I want to mention that there are also samples where receipt no is same but the BPIS no in the table are different. 
       
      Receipt noBPIS NOBPIS DateMapping status Count
      C009352180066251015x28-01-2018MAPPED1
      C009352180066251016x28-01-2018MAPPED1

      So if I apply distinct (table) I will not be able to get the distinct table. 
       
      And if I apply distinct (table. ([receipt no]) I will get the distinct table but what about other columns how I will map the BPIS No and date to the distinct table. 
       
      Please suggest. 
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Kumshan450,

         

        I see. With the table you have posted above you will not be able to use this table to create a relationship on Receipt no as you have multiple instances of the same receipt number. It will help if you can explain why you need to create the relationship and then I will be able to advise, as I can see multiple ways of achiving multiple goals here.

         

        Thanks!

        Kris