cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zipke Frequent Visitor
Frequent Visitor

Create new DAX table based on visualization table and measures

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create new DAX table based on visualization table and measures

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
5 REPLIES 5
Highlighted
Super User
Super User

Re: Create new DAX table based on visualization table and measures

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] 

Community Support Team
Community Support Team

Re: Create new DAX table based on visualization table and measures

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
zipke Frequent Visitor
Frequent Visitor

Re: Create new DAX table based on visualization table and measures

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

Community Support Team
Community Support Team

Re: Create new DAX table based on visualization table and measures

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
zipke Frequent Visitor
Frequent Visitor

Re: Create new DAX table based on visualization table and measures

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!