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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare Positive and Negative Rows in Same Column

Happy Friday,

Can anyone suggest me some idea? I want to compare Balance column rows,script should compare same positive and same negative number (Example:Compare postive 100 with -100 and remove those Rows).

Balance

100

200

300

400

-100

-200

-700

 

Result

Balance

300

400

-700

 

1 ACCEPTED SOLUTION

@Anonymous

 

Try this Calculated table

Please see attached file with your sample data as well

 

 

Calculated Table 2 =
VAR temp1 =
    ADDCOLUMNS ( Table2, "ABS", ABS ( Table2[Balance] ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "MyFilter", COUNTX (
            FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) && [Map] = EARLIER ( [Map] ) ),
            1
        )
    )
VAR temp3 =
    FILTER ( temp2, [MyFilter] = 1 )
RETURN
    SELECTCOLUMNS ( temp3, "Balance", [Balance], "Map", [Map] )

 

I get this result

 

PBI.png


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

With Power Query, you can do something like this
Please see attached file for steps

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Balance", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ABS", each Number.Abs([Balance])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ABS"}, {{"Countrows", each Table.RowCount(_), type number}, {"ALL", each _, type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Balance"}, {"Balance"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ALL", each ([Countrows] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ABS", "Countrows"})
in
    #"Removed Columns"

 

 

 


Regards
Zubair

Please try my custom visuals

@Anonymous

 

Also you can use following Calculated Table using DAX

 

From the Modelling Tab>>New Table

 

Calculated Table =
VAR temp1 =
    ADDCOLUMNS ( Table1, "ABS", ABS ( Table1[Balance] ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "MyFilter", COUNTX ( FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) ), 1 )
    )
VAR temp3 =
    FILTER ( temp2, [MyFilter] = 1 )
RETURN
    SELECTCOLUMNS ( temp3, "Balance", [Balance] )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi Zubair,

I just recieve my dataset and it has duplicate values in Balance Column but i found two columns Sign and Map.

BalanceSignMap
100YUSA
200YUSA
300YChina
400YChina
-100NUSA
-200NUSA
-700NChina
100YChina
BalanceSignMapCommentsResult
100YUSASame negative and posituve number and Sign should be Y&N and Region should be sameDelete
-100NUSASame negative and posituve number and Sign should be Y&N and Region should be sameDelete
100YChina Keep

@Anonymous

 

Try this Calculated table

Please see attached file with your sample data as well

 

 

Calculated Table 2 =
VAR temp1 =
    ADDCOLUMNS ( Table2, "ABS", ABS ( Table2[Balance] ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "MyFilter", COUNTX (
            FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) && [Map] = EARLIER ( [Map] ) ),
            1
        )
    )
VAR temp3 =
    FILTER ( temp2, [MyFilter] = 1 )
RETURN
    SELECTCOLUMNS ( temp3, "Balance", [Balance], "Map", [Map] )

 

I get this result

 

PBI.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thank you so much Zubair.You really saved my Days.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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