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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Kumshan450,

 

I'll talk generally - The easiest way create the table that you desire would be to use the query editor. Load the source table again (as you would have to initially load this to your model). In the query editing mode highlight the 3 columns that you wish to keep distinct rows (hold Ctrl whn clicking each one to highlight multiple) and select Remove Rows > Duplicate Values as shown below

 

 

This will give you a table of all distinct Receipt no/BPIS NO/BPIS Dates.

 

Let me know how you get on.

 

Cheers,

Kris