Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rnagallatav
New Member

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 TimezoneValueCurrencyType
A12ActiveAmerica5000USDRetail
B34inactiveAsia0USDRetail
C45ActiveAsia0USDRetail
D56ActiveAsia1000USDRetail
E67inactiveAmerica0USDTemplate

 

Table - B

IDattribute Titleattribute value
A12close date13-11-2020
B34project partner 
C45FOH area 
B34Project type 
E67FOH area 
B34open date02-02-2021
C45open date20-07-2020

 

Result :

rnagallatav_3-1645614179075.png

Appreciate your support.

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @rnagallatav ,

Here are the steps you can follow:

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

vyangliumsft_0-1646096788367.png

Result:

vyangliumsft_1-1646096788371.png

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))

vyangliumsft_2-1646096788374.png

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

vyangliumsft_3-1646096788374.png

4. Result:

vyangliumsft_4-1646096788378.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @rnagallatav ,

Here are the steps you can follow:

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

vyangliumsft_0-1646096788367.png

Result:

vyangliumsft_1-1646096788371.png

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))

vyangliumsft_2-1646096788374.png

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

vyangliumsft_3-1646096788374.png

4. Result:

vyangliumsft_4-1646096788378.png

 

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
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.