Need Help - Duplicate rows issue

[rnagallatav]

We have a requirement in power BI to create a tabular visual with columns from table A (id,status,type,value) and columns from table B (S_Opening_date,S_Closer_date)

For this I need to create two calculated columns "S_Opening_date" and " S_Close_date",
1) Logic for "S_Opening_date" - From table B, "attribute title" = "open date", the date in "atrribute value" column should be displayed, if available
2) Logic for "S_close_date" - From table B, "attribute title" = "close date", the date in "atrribute value" column should be displayed, if available

I have written the following DAX :
S_opening_date = if( 'Table B'[attribute Title] = "open date", 'Table B'[attribute value])
S_close_date = if( 'Table B'[attribute Title] = "close date", 'Table B'[attribute value])

However, data is getting repeated multiple times after including the calculated columns in the table for rows that have date information available.

Please guide me on how to avoid the duplicate rows and what might be wrong in my approach. Refer the attached sample data

Sample data :

Table A:

ID	Status	Timezone	Value	Currency	Type
A12	Active	America	5000	USD	Retail
B34	inactive	Asia	0	USD	Retail
C45	Active	Asia	0	USD	Retail
D56	Active	Asia	1000	USD	Retail
E67	inactive	America	0	USD	Template

Table - B

ID	attribute Title	attribute value
A12	close date	13-11-2020
B34	project partner
C45	FOH area
B34	Project type
E67	FOH area
B34	open date	02-02-2021
C45	open date	20-07-2020

Result :

Appreciate your support.

[v-yangliu-msft · Accepted solution]

Hi  @rnagallatav ,

Here are the steps you can follow：

1. Enter the power query, Home – Merge Queries – Merge Queries as new – Left Out.

Result:

2. Create calculated column.

Index =
IF(
    'Merge1'[Table B.attribute Title]=BLANK()&&'Merge1'[Table B.attribute value]=BLANK(),0,
RANKX(FILTER(ALL(Merge1),'Merge1'[ID ]=EARLIER('Merge1'[ID ])&&'Merge1'[Status ]=EARLIER('Merge1'[Status ])),[Table B.attribute value],,DESC,Dense))

3. Put [Index] in Filter, is=1.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[HotChilli]

There are no duplicate rows.  There's probably a relationship created on ID and there are 2 entries in Table B for some of the entries in Table A.

-----

To get what you want, I would use Table B as the dimension table and Table A as the Fact table.  It looks like you can Pivot (from the Transform menu) the 'attribute Title' in Table B to get the table you want.

You won't need the calculated columns anymore.

You can then make the relationship from Table B->Table A as 1:m and create the table visual.

--

That will probably work with the sample data but the real data may be more complex.