Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
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
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!
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |