Forum Discussion
Helper IIICreate 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))
Helper IIIHi,
I am not using power query to remove duplicates as
Removing duplicates in power query subsequently slows the process of loading the query in power pivot table.
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
- Kumshan450
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.
- Kumshan450
using dax
- Kumshan450
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))