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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
trevb
Resolver I
Resolver I

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

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...

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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:

 Capture.PNG

Yearinfo:

Capture2.PNG 

 

Relationship:

Capture5.PNG

 

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

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

 Capture3.PNG

 

 

Result:

Capture4.PNG

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

 

 

 

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
Super User
Super User

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:

 

has1value.png

 

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors