DAX Formula (Using Join)

0
votes

I'm looking to create a dax calculated column that uses two related tables. one is a dimension one is a fact. in MDX it looks like this:

Sum( 
   {[Tbl Master Measure Mapping].[Str Busies].[True]}
  ,[Measures].[Int Calls Offered]
)

in t-sql it looks like this:

select int_CallsOffered from fact_CallType_OTS a
inner join tbl_MasterMeasureMapping b on a.entName = b.entName
where b.Str_Busies = 'True'

Pretty Straight forward. This works in a cube no problem. How can i translate the above to a dax formula in power pivot ? would this be a measure or a calculated column ? i'm thinking calcualted column. I've looked on the internet, and a term "Evaluate" comes up, but i don't find that function in my version of power pivot. maybe i'm behind ? but i'd love to find a solution where i join just two tables (which i've defined the relationship on) and get a value back based on the where clause. Thanks.

b

1
excelpowerpivotdax
can you share the table structure? which one is lookup? any real data examples? – Petr Havlik
hi, it's a very large table structure. The lookup table is tbl_MasterMeasureMapping. It contains true or false values. The referencing table is the fact table fact_CallType_OTS. – Junior Vasquez

1 Answers

2
votes

You could create the measure using the CALCULATE function.

Call type OTS total:=CALCULATE(sum([Int Calls Offered]),'Tbl Master Measure Mapping'[Str Busies]=TRUE)

Note: your TSQL and MDX would not return the same thing as the TSQL is not aggregating

see https://msdn.https://msdn.microsoft.com/en-us/library/ee634825.aspx