Forum Discussion
Create new table by filtering column value
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))
Hi Kumshan450,
If you only wish to use DAX and you are using Power Pivot (and not Power BI), the only way I can see to do this would be to
- Create a column that contains a concatenation of the columns Receipt no, BPIS NO and BPIS Date.
- You can that follow the steps from Step 1 in my original post to get a calcualted table that contains a distinct list of the created concat column.
- From here create a relationship between the 2 tables and use the DAX formulas =RELATED([Receipt no]), =RELATED([BPIS NO]) and = RELATED([BPIS Date]) to build the other 3 columns.
- You can then hide the concatenated columns from the model by right clicking these in Diagram View and selecting Hide from Client Tools.
Let me know if this suffices.
Thanks,
Kris
Yes i will check
Is there any I can create a new table from the existing table in power pivot table data model with count value =1. This will definitely give me an accurate distinct table.
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))
using dax
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
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.
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 is same but the BPIS no and dates are different. I don't want to filter this data. I want to remove the same.
Hi Kumshan450,
Perfect! I see that you must be using Power BI then and not Power Pivot?
Congrats and Kudos,
Kris
Hi, It will not filter out the below sample which is also a duplicate
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 no BPIS NO BPIS Date Mapping status Count C00935218006625 1015x 28-01-2018 MAPPED 2 C00935218006625 1016x 28-01-2018 MAPPED 2 However, I applied the calculate table function which is giving me distinct values.
Distinctreceiptdump = CALCULATETABLE(Receiptdump,filter(Receiptdump,Receiptdump[Count]=1))
Why am I totally against removing duplicates in power query stage? Removing duplicate step dont fit large volume of data. I am handlling around 20 lakh rows of data. IF suppose i add additional months and refresh OH god I have to wait for months to load the query in data model.
Yes i am using power BI