cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChiRomeu
Frequent Visitor

Unique value

Dear member,

 could you please kindly help me to solve the following problem:

 I would like to get this result in table of power. 

Best regards

ChiCaptura.PNG

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

HI @ChiRomeu 
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)

1.png

Where 'Data' is the original table

Data New = 
VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) )
VAR T3 = 
    ADDCOLUMNS ( 
        T2, 
        "@UniqueValue", 
        VAR CurrentID = [@MasterID]
        VAR CurrentIndex = [@Index]
        VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] )
        RETURN
            IF ( CurrentIndex = FirstIndex, 1, 0 )
    )
RETURN
    SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

HI @ChiRomeu 
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)

1.png

Where 'Data' is the original table

Data New = 
VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) )
VAR T3 = 
    ADDCOLUMNS ( 
        T2, 
        "@UniqueValue", 
        VAR CurrentID = [@MasterID]
        VAR CurrentIndex = [@Index]
        VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] )
        RETURN
            IF ( CurrentIndex = FirstIndex, 1, 0 )
    )
RETURN
    SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )

Thanks a lot!

Whitewater100
Super User
Super User

Hi:

Assuming your table name = "Data" you can use this caclculated column:

Unique =
VAR CountID =
COUNTROWS(
               FILTER(ALL(Data),
               'Data'[Master_ID] = EARLIER(Data[Master_ID])))
RETURN
IF(CountID >1,0, 1)
 
Whitewater100_0-1662400867629.png

I hope this helps!

Thank you for info.

Is it possible to include the first row about the >2 rows?

 

best regards

 

YEs, like this?

Unique R =
IF(
COUNTROWS(
               FILTER(ALL(Data),
               'Data'[Master_ID] = EARLIER(Data[Master_ID])))
 >1,0, 1)
Whitewater100_0-1662403557853.png

If this helps, please mark as solution.Have a nice day!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors