DAX table joins

• as_1234

  SSCrazy

  Points: 2859

  More actions

  March 27, 2020 at 10:50 am

  #3738195

  All,

  I'm trying to improve my understanding of cubes and DAX but I'm missing something. If possible I would appreciate being pointed in the right direction.

  My model has two tables - Post and PostTypes.

  Post is the fact tables and PostTypes is a measure table.

  There is a link, within the model, between Post.PostTypes and PostTypes.ID

  My understanding is that because the link is in the model I don't need to specify the join in DAX queries?

  I'm using the following query:

  evaluate (
  
  filter(posts,posttypes[id]=1))
  
  

  I'm aiming for the equivalent of:

  select * from posts inner join posttypes on posts.posttype=posttypes.id where posttypes.id=1

  I get the error:

  Query (3, 14) A single value for column 'id' in table 'posttypes' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

  It looks like the DAX query is trying to establish a single value in posts.posttype instead of doing the join and using the posttypes.id column which does have single values? However I don't know why?

  Thanks

   

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  March 28, 2020 at 11:10 am

  #3738609

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• as_1234

  SSCrazy

  Points: 2859

  More actions

  March 31, 2020 at 2:53 pm

  #3739303

  I think I partially figured this out? My understanding is as follows?:

  Some functions in DAX use 'row context' and some use 'filter context'.

  A function using 'row context' can't join tables automatically.

  A function using 'filter context' can join tables automatically.

  The below code solved the issue in my first post:

  evaluate (
  
  filter(posts,related(posttypes[id])=1)
  
  )