Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

Please help me its urgent

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.

pk786_0-1646807205419.png

 

2 ACCEPTED SOLUTIONS

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 

yingyinr_3-1647328570450.png

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] ) )
)

yingyinr_2-1647328527837.png

If the above one can't help you get the desired result, please provide the following required info. Thank you.

  • How did you set these two table visuals(upper and below table visual)? Could you please provide the related Fields pane setting just as below screenshot? 

         yingyinr_1-1647327965695.png

  • Are the data on these two visuals from the same table? If no, is there any relationship created between the tables?

Best Regards

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

View solution in original post

v-yiruan-msft
Community Support
Community Support

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.

I need urgent help

Best Regards

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

View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

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.

I need urgent help

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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

 

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@amitchandak I want 2018 values in 2018 only and 2019 in 2019 only 

pk786_0-1646811234552.png

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 

yingyinr_3-1647328570450.png

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] ) )
)

yingyinr_2-1647328527837.png

If the above one can't help you get the desired result, please provide the following required info. Thank you.

  • How did you set these two table visuals(upper and below table visual)? Could you please provide the related Fields pane setting just as below screenshot? 

         yingyinr_1-1647327965695.png

  • Are the data on these two visuals from the same table? If no, is there any relationship created between the tables?

Best Regards

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.