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 no ...
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))

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Kumshan450,

 

I am not sure you are able to create a calculated table of multiple columns in Power Pivot. In Power BI you can create a calculated talble to do this using the DAX formula

 

Table = SUMMARIZE(Receiptdump,Receiptdump[Receipt no],Receiptdump[BPIS NO],Receiptdump[BPIS Date])

 

Using Power Pivot and DAX I'd suggest trying my method above. Otherwise maybe biting the bullet and creating this in the query editor. How many rows of data are we talking that causes the refresh to take so long?

 

Best,

Kris

Kumshan450's avatar
Kumshan450
Helper III
8 years ago

Its 20 lakh rows of data.

 

Also i dont want unique combinations. I want unique receipt no. 

 

IF i apply the formula 

Table = SUMMARIZE(Receiptdump,Receiptdump[Receipt no],Receiptdump[BPIS NO],Receiptdump[BPIS Date])

 

IT will show the unique combinations of receipt no|Receipdate|BPISno.

 

I want to remove all the duplicate receipt no and concentrate only on the unique receipt no. 

 

I have mentioned in the post that there are also samples where receipt no are same but the BPIS no and dates are different. I dont want to filter this data. I want to remove the same.