Table visual, measures and relationships - why do I not get what I expect

[trevb]

I know this should be obvious but I cannot get my table and measures to work out.  If I get my brain around this then I will finally start to get measures so I'm asking for help at risk of looking like a twit.

For the test purposes I am using a measure that currently just looks like this:

Measure := if(HASONEVALUE(Projects[ProjectCode]),"One Value","Multi Value")

I have two tables linked together with an ID called "ProjectCode" These values are unique and both tables are based on the same core table so they should be identical.  One is called "Projects" and has stuff about the projects the other is called "YearInfo" and is the list of project codes with a column for each financial year going from 2011 to 2021 that contains the number of days worked or to be worked.  The two tables have a one to one relationship around "ProjectCode"

Now if I create a table in power bi and put in "Project code" and each of the financial years from "YearInfo" I get the table I expect with each "ProjectCode" having one row that has 11 columns each with a value in it something like this

ProjectCode1    1    2    3    4   5   6   7   8   9   10

ProjectCode2    1    2    3    4   5   6   7   8   9   10

ProjectCode3    1    2    3    4   5   6   7   8   9   10

....

That's great nearly there.  I want though each row to have the project name so I drop in the project name from the "Projects" table.  As there is a one to one relationship what I expect to get is:

ProjectCode1    "OneValue" 1    2    3    4   5   6   7   8   9   10

ProjectCode2    "OneValue" 1    2    3    4   5   6   7   8   9   10

ProjectCode3    "OneValue" 1    2    3    4   5   6   7   8   9   10

What I actually get assuming I have three projects (in reality there are many more) is 

ProjectCode1   "Multi Value"    1    2    3    4   5   6   7   8   9   10

ProjectCode1   "Multi Value"   1    2    3    4   5   6   7   8   9   10

ProjectCode1   "Multi Value"   1    2    3    4   5   6   7   8   9   10

ProjectCode2   "Multi Value"   1    2    3    4   5   6   7   8   9   10

ProjectCode2   "Multi Value"   1    2    3    4   5   6   7   8   9   10

ProjectCode2   "Multi Value"    1    2    3    4   5   6   7   8   9   10

ProjectCode3   "Multi Value"    1    2    3    4   5   6   7   8   9   10

ProjectCode3   "Multi Value"    1    2    3    4   5   6   7   8   9   10

ProjectCode3   "Multi Value"    1    2    3    4   5   6   7   8   9   10

I can see what this is but why does the relationship in the table not sort this out?

Humbly looking for enlightenment...

[v-shex-msft]

Hi trevb,

According to your description, the table ‘projectcode’ and ‘yearinfo’ have one-one relationship based on [ProjectCode]. And the table ‘yearinfo’ also contains the separate field for each year, right?

If that is a case, the measure like yours will return ‘One Value’ instead of ‘Multi Value’. Please refer to below test steps and results:

1. Create two tables.

ProjectCode:

 

Yearinfo:

 

Relationship:

 1. Add the measure to ‘projectcode’ table and create the table visual:

Measure = if(HASONEVALUE(ProjectCode[ProjectCode]),"One","Muti")

 

Result:

It seems like smoupre’s result.(I try to modify the measure to ‘Measure = if(HASONEVALUE(YearInfo[ProjectCode]),"One","Muti")’ but shows the same result)

Did you use the ‘crossjoin function’ on these tables? Could you share us the dax formula which you use to create the new table?

Regards,

Xiaoxin Sheng

[trevb]

I generated the Year info table in power query.  It just cuts out the ProjectCode column from projects along with start and end date.  It then adds rows for each project for every year with a figure for days of the project in that year then pivots them to get the necessary columns.  No complicated joins or anything any where and Project code should retain a one to one relationship.

Projects itself comes straight out of Project Online so I wanted to keep It pure. Now it works my biggest issue is optimising it all as it is exception slow!

[trevb]

Sorry Smoupre/xiaoxin,

I didn't get a notification of your replies so I have only just seen this.

By using the Projects[Project code] field instead of the one from year info as you suggested the issue went away.  Seemed strange to me because of the one to one relationship, but that seemed to work. 

Many thanks for your help.

Trevor

[Greg_Deckler]

Hmm, I can't seem to replicate this either way in Desktop. I created two tables with Enter Data queries:

ProjectCodes

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLnHOT0k1VIrVQREwQhcwVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectCode", type text}})
in
    #"Changed Type"

YearInfo

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLnHOT0k1UtJRMgRiEG0MxCZAbArEZkBsDsQWQGwJUmOgFKuDotOYbJ2GJOiMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectCode = _t, Y1 = _t, Y2 = _t, Y3 = _t, Y4 = _t, Y5 = _t, Y6 = _t, Y7 = _t, Y8 = _t, Y9 = _t, Y10 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectCode", type text}, {"Y1", Int64.Type}, {"Y2", Int64.Type}, {"Y3", Int64.Type}, {"Y4", Int64.Type}, {"Y5", Int64.Type}, {"Y6", Int64.Type}, {"Y7", Int64.Type}, {"Y8", Int64.Type}, {"Y9", Int64.Type}, {"Y10", Int64.Type}})
in
    #"Changed Type"

Related One:One on ProjectCode.

Regardless if I use ProjectCode from ProjectCodes or YearInfo, I get "OneValue" displayed. My table has the following columns:

[Greg_Deckler]

I believe that the issue is that your measure needs to use "YearInfo[ProjectCode]" instead of "Projects[ProjectCode]". It looks like you are contextually filtering down YearInfo but not Projects but can't really tell unless you share the configuration of your table.