I have an Excel 2013 pivot table on a tabular model data connection. I essentially want to slice the facttable.sales field by a calculated field on a product dimension that doesn't exist in the model. Using my TSQL background to explain the field I want, the only way I know how:
Case when productdimension.category = A then 'sourcesystem1'
else 'sourcesystem2'
Can you write DAX in the "MDX Calculated Member" option in excel's OLAP tools for tabular models ? Would I use this feature for what I want to do ?
Depending if you can write DAX or need to write MDX for this, is what I described possible / how would I write that?
I assume this should be done upstream on the ETL /model side of things but this is for a 1 off report. Additionally, I would like to know if this is possible generally speaking.
You can add a calculated member for this in Excel. This is probably fine, as long as it really is a one-off report. If you find that other people start using this same field, you should really at least add it as a calculated column in the tabular model, if not add it into the ETL earlier in the process.
That said, you can write MDX for a calculated a measure in Excel. This works for Excel 2013 and 2016. I don't have older versions to test.
As you suggested, go to OLAP Tools -> MDX Calculated Measure. The MDX for the calculated measure would be would be
Case [ProductionDimension].[Category].CurrentMember.MEMBER_CAPTION
WHEN 'A' THEN 'sourcesystem1' Else 'sourcesystem2' END