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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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