DISTINCT TABLE USING DAX MEASURES

[Kumshan450]

I have a file named receipt data with duplicates receipt no. Sample data

RECEIPT_NO	BPIS_NO	BPIS_VALUE_DATE
C0597141800083	SBIN618127361534	07-May-18
C0476041801381	36867900	23-Apr-18
C0476041801379	36867900	23-Apr-18
C0476041801381	36867900	23-Apr-18
C0476041801379	36867900	23-Apr-18

I know that we can remove duplicates using power query – simple step.

But I cannot perform the simple step due to the large data model.

I compared the query loading to data model with and without removing duplicates.

It takes hell lot of time when the data is loaded to power query after removing duplicates in power query.

Data loads faster if removing duplicates steps not performed in power query.

So after loading the receipt data containing duplicates in power pivot, I created a distinct table with unique receipt no.

RECEIPT_NO

C0597141800083

C0476041801381

C0476041801379

Now I don’t know how to get the additional columns of receipt data in the distinct table. I tried to do with related function but it does not work.

Output required. 

RECEIPT_NO	BPIS_NO	BPIS_VALUE_DATE
C0597141800083	SBIN618127361534	07-May-18
C0476041801381	36867900	23-Apr-18
C0476041801379	36867900	23-Apr-18

[Ashish_Mathur]

Hi,

If you only have 1 Table, you do not need to create a seperate receipts table.  In a Table visual, i simply dragged the Receipt_No field.  I then wrote these two measures

Date = MIN(Data[BPIS_VALUE_DATE])

BPIS Number = FIRSTNONBLANK(Data[BPIS_NO],1)

Hope this helps.

[Kumshan450]

In this case i have two table 

One table contains the collection data. This also contains duplicate receipt no. 

Loan account no | receipt no | receipt date. 

Another table contains the receipt dump. Which i have shared in my earlier problem..

Purpose I have to create a relationship between the collection data and receipt dump/distinct receipt dump to find the difference between collection date and BPIS date. 

If i create table visual, I am not able to create any relationship between table visual data and the collection data. 

[Greg_Deckler]

Why not just create a relationship between your two tables? 

[Kumshan450]

Yes I tried to create relationship but unable to fetch values from main receipt dump data to distinct receipt dump data like BPIS no and BPIS data