Please help me its urgent

[Anonymous]

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.

[v-yiruan-msft · Accepted 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.

• 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? 

         

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

Best Regards

[v-yiruan-msft · Accepted solution]

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

[amitchandak]

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

@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?

[v-yiruan-msft · Accepted 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.

• 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? 

         

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

Best Regards