Cant create a direct active relationship

[Anonymous]

Hello,

I am trying to set up a splicer that filters data from two different excel work sheets based on year (Image 1). The splicer correctly shows the number of contractors for the year 2020. However, when I change the splicer to 2021 it doesn't display any number. I tried setting up another relationship between spreadsheet 1 (image 2) and spread 2 (image 3)for the year 2021. However, I keep getting the error in image (4). Any help would be greatly appreciated and let me know if you have any questions.

[Anonymous]

So far I got this to work by splitting the tables and creating a relationship between the data. Any idea on how to merge these two tables? So that it displays staff who worked during 2020 or 2021?

[Icey · Accepted solution]

Hi @Anonymous ,

Please try to transform your table like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JTEszVNJRMjIwMgBSIGwIpmN1oLJGeGWN8cqaQIWBKgzhKuCypnj1mkGFERguZY5XowVeWUu8soYGuG01NMTrG0OkgIKqMERTYYzfABNMu2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Staff = _t, #"Year 2020" = _t, #"Year 2021" = _t, #"Count of Year 2020" = _t, #"Count of Year 2021" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Staff", type text}, {"Year 2020", Int64.Type}, {"Year 2021", Int64.Type}, {"Count of Year 2020", Int64.Type}, {"Count of Year 2021", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Count of Year 2020", "Count of Year 2021"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Staff"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Year ","",Replacer.ReplaceText,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Year"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Year", Int64.Type}})
in
    #"Changed Type1"

Then you can get this:

Best Regards,

Icey

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

[Anonymous]

Thank you so much Icey for the video too!!

[Anonymous]

BUMP

[Anonymous]

@jaideepnema I have not. How should I unpivot it? I tried in PowerBI but the data became wierd. I am sure I'm not doing it right. I tried to unpivot in power query editor.

[jaideepnema]

@Anonymous is it possible for you to share a sample pbix with sensitive data removed ?

[Anonymous]

@jaideepnema I tried unpivoting the two year columns but it ended up just duplicating the names regardless if they worked during 2020/2021.

[jaideepnema]

Hi @Anonymous ,

Have you tried unpivot the table(image 3) and then create a active relationship with the unpivoted column. If thats not possible you can create a inactive relationship and then use the userelationship dax function to create a measure to be used in the report.

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[Anonymous]

Hi @jaideepnema,

I have not tried to unpivot the table. I am worried if I do so then the table wont update when new data is entered into the "master" table.

[jaideepnema]

Hi @Anonymous ,

Are you unpivotting this data in Power BI then it shouldnt be problem.