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
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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.