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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ChiRomeu
Helper I
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

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
Solution Sage
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)
 
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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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