Forum Discussion
Need Help - Duplicate rows issue
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.
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
2 Replies
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.
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
