Create new DAX table based on visualization table and measures

[zipke]

Hi all,

I have a huge table with many measures in my visualization.

I now want to create e New table in DAX based on specific columns of this table. However when I try to create the new table I do not have the option to SUMMARIZE on a measure.

As this doesn't work, I tried to create the table and then only using this Measure to to filter, but when I do this, I get the error:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

The code I am trying to use is:

Migration table = 
CALCULATETABLE( 
  SUMMARIZE(
      'File',
      'File'[Item number],
      'File'[Status]
  ),
  'File'[Candidate]="no"
)

I could not get the measure 'File'[Candidate] as a column in the table, so I tried to filter on it, but then I get the error.

How can I solve this?

Thanks in advance!

[v-shex-msft]

HI @zipke ,

AFAIK, measures can't be user as group columns of summarize function, 

In my opinion, I'd like to suggest you create a summarized table based on grouping column fields and add measure as expressions fields. (notice: you can use 'selectcolumns' function to remove these group columns)

Sample:

NEW =
SELECTCOLUMNS (
    FILTER (
        SUMMARIZE (
            Table,
            Table[Group1],
            Table[Group2],
            Table[Group3],
            "Measure1", [Measure1],
            "Measure2", [Measure2],
            "Measure3", [Measure3]
        ),
        [Measure1] = "no"
    ),
    "Measure1", [Measure1],
    "Measure2", [Measure2],
    "Measure3", [Measure3]
)

Notice: when you use measures in calculated column/table, these row level contents will be fixed and they not dynamic affected by external filter/slicer.

Regards,
Xiaoxin Sheng

[zipke]

Dear,

Thank you for your reply. I now managed to create a summarized table based on the measures 1, 2 and 3.

However, the other columns are not visible now, Table[Group1], Table[Group2], Table[Group3] are not in this table, only measure 1, 2 and 3. I added the other columns again in the last section as well and now they are visible in the table. Is that how I should do it, mention it in the code twice?

Next to this, how can I add another FILTER, based on a column, not on a measure? It should be something like this, but it doesn't work:

NEW =
SELECTCOLUMNS (
    FILTER (
        SUMMARIZE (
            Table,
            Table[Group1],
            Table[Group2],
            Table[Group3],
            Table[Status],
            "Measure1", [Measure1],
            "Measure2", [Measure2],
            "Measure3", [Measure3]
        ),
        [Measure1] = "no",
        Table[Status]=1,
    ),
    "Group1", Table[Group1],
    "Group2", Table[Group2],
    "Group3", Table[Group3],
    "Measure1", [Measure1],
    "Measure2", [Measure2],
    "Measure3", [Measure3]
)

How can I accomplish this?

Thank in advance,

Zipke

[v-shex-msft · Accepted solution]

Hi @zipke ,

You can add it to filter function expressions:

NEW Table=
SELECTCOLUMNS (
    FILTER (
        SUMMARIZE (
            Table,
            Table[Group1],
            Table[Group2],
            Table[Group3],
            Table[Status],
            "Measure1", [Measure1],
            "Measure2", [Measure2],
            "Measure3", [Measure3]
        ),
        [Measure1] = "no"
            && [Status] = 1
    ),
    "Group1", Table[Group1],
    "Group2", Table[Group2],
    "Group3", Table[Group3],
    "Measure1", [Measure1],
    "Measure2", [Measure2],
    "Measure3", [Measure3]
)

Regards,

Xiaoxin Sheng

[zipke]

Thank you v-shex! That did the trick!

Do you also know how I can summarize in the same table on multiple tables? I also need columns from Table2 in this summarized table. How can I manage?

Thanks in advance!

[AlB]

Hi @zipke 

The simplified syntax for the filter argument in CALCULATETABLE does not support the use of a measure. You can avoid the error you're getting by using FILTER instead:

Migration table = 
FILTER( 
  SUMMARIZE(
      'File',
      'File'[Item number],
      'File'[Status]
  ),
  'File'[Candidate]="no"
)

It is considered  best practice to reference measures without the table name in front of them, i.e., [Candidate] instead of 'File'[Candidate]