trying to get unique value with result from IF statement

[Slm584]

Hello,
trying to create new column with following if statement:

Contract Title = IF('table'[UCC_RO] = TRUE(), 'table'[Project_Title], 'table'[Contract_Title])

However, need to have (when True) table'[Project_Title] field unique only.

thank you all in advance,

Slm584

[v-yingjl · Accepted solution]

Hi @Slm584 ,

If the 'unique only' represents the max/min value of the column, you can try this dax formula:

Contract Title = 
IF (
    'Table'[UCC_RO] = TRUE (),
    CALCULATE (
        MAX ( 'Table'[Project_Title] ),
        ALLEXCEPT ( 'Table', 'Table'[UCC_RO] )
    ),
    'Table'[Contract_Title]
)

If it represents like the first value of the column, you can try this query in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclbSUSopKk0FUgEhhkDSGUjG6kQrOQLZaYk5xRAZI7CMEVjGCVOLMVjCFVnCGCxhApZwQTHLBCxjqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, UCC_RO = _t, Project_Title = _t, Contract_Title = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"UCC_RO", type logical}, {"Project_Title", type text}, {"Contract_Title", type text}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"UCC_RO"}, 
            {
                {"Data", each 
                Table.AddIndexColumn(_,"Index",1), 
                type table [Category=nullable text, UCC_RO=nullable logical, Project_Title=nullable text, Contract_Title=nullable text, Index = nullable number]
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Category", "Project_Title", "Contract_Title", "Index"}, {"Category", "Project_Title", "Contract_Title", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [UCC_RO] = true then Table.SelectRows(#"Expanded Data", each [Index] = 1 and [UCC_RO] = true)[Project_Title]{0} else [Contract_Title], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Category", "UCC_RO", "Project_Title", "Contract_Title", "Custom"})
in
    #"Reordered Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @Slm584 

Can you show some sample data to illustrate what you are looking for? It's not quite clear now

Is it a calculated column or a measure? Is this in PQ or DAX?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.