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,
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
Hi,
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
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.
using dax