Find the first non blank value and repeat it for the same ID

[ahmedaldafaae]

Please help in wrting a dax measure . TPID has multiple managers (MWA) and I need to repeat the first manager for the same tpid. as shown below 

 TPID       MWA              Desired Values

1	Jame	Jame
1	Billy	Jame
1	Camy	Jame
1	Billy	Jame
2		Billy
2	Billy	Billy
2	Camy	Billy
2		Billy
3	Ammar	Ammar
3	Ammar	Ammar
3		Ammar
3	Camy	Ammar

[Eyelyn9 · Accepted solution]

Hello @ahmedaldafaae ,

According to my understanding, you want to use the first non-blank value in each id group as the value of the same group, right?

You can use the following formula after adding an index column:

Measure =
VAR _firstnoblank =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[TPID] = MAX ( 'Table'[TPID] )
                && 'Table'[MWA] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[MWA] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _firstnoblank )
    )

My visualization looks like this:

Here's the pbix file.

Have I answered your question? Please mark my answer as a solution. Thanks a lot.
If not, load some samples of insensitive data and expected output.

Best regards
Eyelyn Qin

[ahmedaldafaae]

Thank you

It simply works!! 

[Fowmy]

@ahmedaldafaae 

I tried a Power Query solution adding just one custom column:

Paste the below code in a blank Query in the Advanced Editor and check the step added.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzE1VitWBcJwyc3Iq4TznxNxKLFJGQJ4CnIUqDteCUGQMZDnm5iYW4eAhVEE0xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TPID = _t, MWA = _t]),
    #"Added Custom" = Table.AddColumn(Source, "New MWA", (r)=>  List.First(Table.SelectRows(Source, each [TPID]  = r[TPID] and [MWA] <> " ")[MWA]))
in
    #"Added Custom"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AllisonKennedy]

Is there another column that determines the order - we need a way to tell Power BI which value is 'first'. If you don't have a column for this, use Power Query Editor to add an Index column.

Then you can use something similar to this as a calculated column:

MWA Desired =
VAR _ThisTPID = 'Table'[TPID]
VAR _tblTPIDvalues = FILTER(ALL('Table'), 'Table'[TPID] = _ThisTPID && NOT(ISBLANK('Table'[MWA])))
VAR _Index = MINX(_tblTPIDvalues, 'Table'[Index])
RETURN
MINX(FILTER(_tblTPIDvalues, _Index = 'Table'[Index]), 'Table'[MWA])