March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hy all can anybody tell me how to do this if my ca value in upper table is 2018 then how to make that 2018 in below table also because only 1 is getng correct other is same eg if its 2018 then i am getting ca and dc and cd in 2018 wrt there differnet values so please suggest me that how all differenet value lies in diff year.
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Split the columns CD and CA with year and value in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTbagIxEIZfRbxuYQ7JNundekRoVGoLPXlhqReWquCh0LdvsnEXBw+rtbAkQ5bMxz/zT15fqy0HWL2qpsvlePr+9VN5WwNQUnkYL1f+uK/80mrnp3e9dmfw0KkP/GnjPnV+c5PZfOH3dsMNAIAthSvFp+iaAI2PNMZoeJVB6QiUQ96m20BuK+l6NZ/OV5Pvcfgx+twCEiibbAM5AAlClMRoA+STgN3e/Q4DjAHBoJAZbc7w0Yah/sxApkQWzuYMKeMChNIahIy8UlYQdIkd3FN6qP0I2rLoRlEpJWUkZTLcc1ino9nHrhBDrIUQPNDzUsqgdphCBKJcShVdVydTVKGlm3Ybu/NiNNj91lIorHXzP/OCrFiqKnzGQpS5qHSIeFLpwlFrMZ+tKs0sT5a6lday6+XDb8BQ9sj44QEOd18AupGJOpBCHuIYRSbCIabesrXg+Nk0SeRY5Kx+j9a4jcVtzlFnco5qY19EjkyjiK1kUsEEwTz2lJeOVfCG6BrstcZRiCqs6Or9Pe8DoHytubC73bY70vlN0mxjwRgVJ7JJmetCThOD4fAX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FailureMode = _t, FunctionalGroup = _t, Priority = _t, Site = _t, ProductFamily = _t, Deviation = _t, GDMSNumber = _t, RMA = _t, DID = _t, DC = _t, CD = _t, CA = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FailureMode", type text}, {"FunctionalGroup", type text}, {"Priority", type text}, {"Site", type text}, {"ProductFamily", type text}, {"Deviation", type text}, {"GDMSNumber", type text}, {"RMA", Int64.Type}, {"DID", type text}, {"DC", type text}, {"CD", type text}, {"CA", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "CD", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"CD", "CD_Year"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"CD", Int64.Type}, {"CD_Year", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "CA", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"CA", "CA_Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"CA", Int64.Type}, {"CA_Year", Int64.Type}})
in
#"Changed Type2"
2. Create the measures to get the related values
NEW_CA =
CALCULATE (
SUM ( 'Table'[CA] ),
FILTER ( 'Table', 'Table'[CA_Year] = SELECTEDVALUE ( 'Date'[mfg_year_no] ) )
)
NEW_CD =
CALCULATE (
SUM ( 'Table'[CD] ),
FILTER ( 'Table', 'Table'[CD_Year] = SELECTEDVALUE ( 'Date'[mfg_year_no] ) )
)
If the above one can't help you get the desired result, please provide the following required info. Thank you.
Best Regards
Hi @Anonymous ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
And there is your another thread which has the same requirement with this one, could you please also mark it as Answered if the problem has been resolved? Thank you.
Best Regards
Hi @Anonymous ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
And there is your another thread which has the same requirement with this one, could you please also mark it as Answered if the problem has been resolved? Thank you.
Best Regards
@Anonymous , Based on what I got. Split CA and Cd in power query and you will get year
Split Column Power Query: https://youtu.be/FyO9Vmhcfag
If both years are the same then you can use one of them. Else create a year table and join with both of them. One join will be inactive which you can activate using userelationship
@amitchandak I want 2018 values in 2018 only and 2019 in 2019 only
In this cd should be in 2019 but its in 2020 why, so I want it should show cdin 2019 and ca in 2020 like that , do you have any suggestions?
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Split the columns CD and CA with year and value in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTbagIxEIZfRbxuYQ7JNundekRoVGoLPXlhqReWquCh0LdvsnEXBw+rtbAkQ5bMxz/zT15fqy0HWL2qpsvlePr+9VN5WwNQUnkYL1f+uK/80mrnp3e9dmfw0KkP/GnjPnV+c5PZfOH3dsMNAIAthSvFp+iaAI2PNMZoeJVB6QiUQ96m20BuK+l6NZ/OV5Pvcfgx+twCEiibbAM5AAlClMRoA+STgN3e/Q4DjAHBoJAZbc7w0Yah/sxApkQWzuYMKeMChNIahIy8UlYQdIkd3FN6qP0I2rLoRlEpJWUkZTLcc1ino9nHrhBDrIUQPNDzUsqgdphCBKJcShVdVydTVKGlm3Ybu/NiNNj91lIorHXzP/OCrFiqKnzGQpS5qHSIeFLpwlFrMZ+tKs0sT5a6lday6+XDb8BQ9sj44QEOd18AupGJOpBCHuIYRSbCIabesrXg+Nk0SeRY5Kx+j9a4jcVtzlFnco5qY19EjkyjiK1kUsEEwTz2lJeOVfCG6BrstcZRiCqs6Or9Pe8DoHytubC73bY70vlN0mxjwRgVJ7JJmetCThOD4fAX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FailureMode = _t, FunctionalGroup = _t, Priority = _t, Site = _t, ProductFamily = _t, Deviation = _t, GDMSNumber = _t, RMA = _t, DID = _t, DC = _t, CD = _t, CA = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FailureMode", type text}, {"FunctionalGroup", type text}, {"Priority", type text}, {"Site", type text}, {"ProductFamily", type text}, {"Deviation", type text}, {"GDMSNumber", type text}, {"RMA", Int64.Type}, {"DID", type text}, {"DC", type text}, {"CD", type text}, {"CA", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "CD", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"CD", "CD_Year"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"CD", Int64.Type}, {"CD_Year", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "CA", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"CA", "CA_Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"CA", Int64.Type}, {"CA_Year", Int64.Type}})
in
#"Changed Type2"
2. Create the measures to get the related values
NEW_CA =
CALCULATE (
SUM ( 'Table'[CA] ),
FILTER ( 'Table', 'Table'[CA_Year] = SELECTEDVALUE ( 'Date'[mfg_year_no] ) )
)
NEW_CD =
CALCULATE (
SUM ( 'Table'[CD] ),
FILTER ( 'Table', 'Table'[CD_Year] = SELECTEDVALUE ( 'Date'[mfg_year_no] ) )
)
If the above one can't help you get the desired result, please provide the following required info. Thank you.
Best Regards
User | Count |
---|---|
122 | |
99 | |
89 | |
73 | |
65 |
User | Count |
---|---|
138 | |
115 | |
115 | |
98 | |
98 |