DAX: How to implement CREATE MEMBER MDX function in SSAS Tabular Model

Question

I have a task to convert existing MDX measures (from multidimensional model) into DAX (tabular model). During this task I found that in DAX there is no functionality to use "CREATE MEMBER" option as it was in MDX. But I have some members created inside this cube by this function (not in DWH). In this case I'm trying to figure out how to do the same (equivalent) in tabular model (DAX)

There is a part of code which I'm replacing right now:

CREATE MEMBER CURRENTCUBE.[Condition].[Condition].[All].[NEW+USED]
 AS [Condition].[Condition Type].[NEW]+[Condition].[Condition Type].[USED]

Image:

There is an example of [Condition] table from DWH:

I have an idea to create a VIEW based on this table with UNION to add a new row "NEW+USED" inside this VIEW and than use SWITCH inside cube (DAX function) for ALL measures

For example:

NVC:= 
VAR GALC = [ABC] + [CDE]
RETURN SWITCH(
                    SELECTEDVALUE('Condition'[ConditionTotal]);
                    "ConditionTotal"; GALC;
                    "NEW+USED"; CALCULATE(
                                               GALC;
                                               FILTER(ALL('Condition'[ConditionDescription]); 'Condition'[ConditionDescription] = "New" && 'Condition'[ConditionDescription] = "Used")
                                               )
                )

But I'm not sure if it is correct way or not because in this case I should populate all columns from table and some of them using in relationships to Fact tables inside cube.

Hello. Do you found the solution. I have the same issue. May be it could help me. — Lidou123

user1523087 user1523087 · Accepted Answer · 2019-04-23T10:12:41

The only one good solution which I found for now in general looks like this:

Create a new database object (VIEW, for example) in your database with full list of recurred members (so the members will be created on database side instead of SSAS).
Create measure and add additional logic for these members inside your measure.

For example of measure:

My_measure:= 
VAR New_var  = CALC(SUM('FactTable'[Price]), 'Condition'[ConditionDescription] = "New")
VAR Used_var = CALC(SUM('FactTable'[Price]), 'Condition'[ConditionDescription] = "Used")
VAR New_and_Used_var = CALC(SUM('FactTable'[Price]),  ALL('Condition'), 'Condition'[ConditionDescription] in {"New","Used"})

RETURN   
  SWITCH (SELECTEDVALUE('Condition'[ConditionDescription]),   
         "New", New_var, 
         "Used",Used_var,
         "New + Used", New_and_Used_var
         )

The only one thing which I additionally did is I make reference between my "FactTable" and this new database object (View) as INACTIVE (unchecked "Active" flag in reference window) because it worked not correctly with this flag.

DAX: How to implement CREATE MEMBER MDX function in SSAS Tabular Model

1 Answers