cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

Chi

1 ACCEPTED SOLUTION
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)

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 =
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] )
6 REPLIES 6
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)

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 =
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] )
Helper I

Thanks a lot!

Solution Sage

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)

I hope this helps!

Helper I

Thank you for info.

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

best regards

Solution Sage

YEs, like this?

Unique R =
IF(
COUNTROWS(
FILTER(ALL(Data),
'Data'[Master_ID] = EARLIER(Data[Master_ID])))
>1,0, 1)

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

Super User

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors