0

I have a tabular model where there are some M:M relationships established. I created a measure that did something like M_A = CALCULATE(DISTINCTCOUNT('TableA'[ColA1])) This measure slices just fine in a pivot table when I use DimDate columns, TableB and TableC columns.

However, when I try to issue a DAX query in SSMS like below, it gives me an error.

EVALUATE(

SUMMARIZE

    (
        FILTER(DimDate,[ColDate3]>VALUE("1/1/2016"))
        ,[ColDate1]
        ,[ColDate2]
        ,[ColB1] --OR [ColC1] for that matter
        ,"SomeName"
        ,[M_A]
    )
))ORDER BY [ColDate1],[ColDate2] DESC `

What I don't understand is how excel can slice this measure by all DimDate, TableB and TableC columns but when I run above query, here is the message I get from ssms.

Executing the query ... Query (1, 9) The column 'ColB1' specified in the 'SUMMARIZE' function was not found in the input table. Run complete

Is there a way for me to run a profiler and intercept what excel pivot table is passing to dax engine so I can replicate the query on ssms and understand what I am doing wrong while constructing my query?

Many thanks for your help!

example diagram

chi
  • 471
  • 3
  • 18

1 Answers1

1

Yes, run the sql profiler, only connect to analysis services rather than the database engine from the drop down; however, the trace will capture MDX statements and not DAX because Excel talks to SSAS via MDX. If you do the same from power bi desktop and take the trace you will get the queries in DAX.

You can also install OLAP Pivot Table Extensions which will tell you the MDX queries Excel is sending to SSAS for a given pivot table.

Travis
  • 241
  • 1
  • 10