Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

     

    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

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous 

     

    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] 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        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