Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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:
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
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:
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
Thank you
It simply works!!
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |