Hello all,
I am developing a report in Power BI Report Builder and I am running into a syntax error that formats just fine on daxformatter, so it must be something else. The issue is with VAR Clusters. Please see below for the query. Interesting enough, if you remove VAR Clusters and VAR __FilterCluster, the query compiles just fine. I have been copy pasting the Circuit section and replacing it with my Cluster stuff. Circuits and Clusters are defined to have the same inputs and the query's are practically identical ( a little different in the Hierarchy order). Any help is greatly appreciated because I am losing my mind at this point.
I renamed "Clusters" to "Cluxter" and it works. Real curious as to why Clusters is not accepted?
DEFINE
VAR __FilterYearTerm =
TREATAS ( { @YearTerm }, 'YearTerm'[YearTerm] )
VAR __FilterDistrict =
TREATAS ( { @district }, 'System'[District] )
VAR Circuits =
UNION (
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'System'[Circuit] ),
__FilterDistrict,
'System'[Circuit] <> BLANK ()
),
"Circuits[Seq]", 2
),
FILTER ( { ( @Circuit, 1 ) }, [Value1] <> "All" )
)
VAR __FilterCircuit =
TREATAS (
SUMMARIZE (
FILTER ( Circuits, Circuits[Seq] = MINX ( Circuits, Circuits[Seq] ) ),
[Circuit]
),
System[Circuit]
)
VAR Clusters =
UNION (
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'System'[Cluster] ),
__FilterCircuit,
'System'[Cluster] <> BLANK ()
),
"Clusters[Seq]", 2
),
FILTER ( { ( @Cluster, 1 ) }, [Value1] <> "All" )
)
VAR __FilterCluster =
TREATAS (
SUMMARIZE (
FILTER ( Clusters, Clusters[Seq] = MINX ( Clusters, Clusters[Seq] ) ),
[Cluster]
),
System[Cluster]
)
VAR Schools =
UNION (
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'System'[SystemKey] ),
__FilterCluster,
'System'[SystemKey] <> BLANK ()
),
"Schools[Seq]", 2
),
FILTER ( { ( @School, 1 ) }, [Value1] <> -99 )
)
VAR __FilterSchool =
TREATAS (
SUMMARIZE (
FILTER ( Schools, Schools[Seq] = MINX ( Schools, Schools[Seq] ) ),
[SystemKey]
),
System[SystemKey]
)
VAR Phases =
UNION (
FILTER (
SUMMARIZECOLUMNS ( Grade[Phase], "Phases[Seq]", 2 ),
Grade[Phase] <> BLANK ()
),
FILTER ( { ( @Phase, 1 ) }, [Value1] <> "All" )
)
VAR __FilterPhase =
TREATAS (
SUMMARIZE (
FILTER ( Phases, Phases[Seq] = MINX ( Phases, Phases[Seq] ) ),
[Phase]
),
Grade[Phase]
)
VAR Grades =
UNION (
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( Grade[Grade] ),
__FilterPhase,
Grade[Grade] <> BLANK ()
),
"Grades[Seq]", 2
),
FILTER ( { ( @Grade, 1 ) }, [Value1] <> -99 )
)
VAR __FilterGrade =
TREATAS (
SUMMARIZE (
FILTER ( Grades, Grades[Seq] = MINX ( Grades, Grades[Seq] ) ),
[Grade]
),
Grade[Grade]
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
'YearTerm'[YearTerm],
'System'[SystemKey],
'System'[Province],
'System'[Region],
'System'[District],
'System'[Circuit],
'System'[Cluster],
'System'[SchoolName],
'LearnerChart'[LearnerKey],
'LearnerChart'[Surname],
'LearnerChart'[FirstName],
'LearnerChart'[TermPromotion],
'Grade'[Grade],
'Grade'[Description],
'Subject'[Description],
__FilterGrade,
__FilterCircuit,
__FilterCluster,
__FilterSchool,
__FilterYearTerm,
"ReportMarks", [ReportMarks],
"DaysAbsent", CALCULATE ( AVERAGE ( 'LearnerChart'[DaysAbsent] ) ),
"AverageReportMark", CALCULATE ( AVERAGE ( 'LearnerChart'[Mark] ) ),
"ReportMarkLevel", CALCULATE ( AVERAGE ( 'LearnerChart'[RmLevel] ) ),
"AverageFailures", CALCULATE ( AVERAGE ( 'LearnerChart'[Failures] ) ),
"AverageNearFailures", CALCULATE ( AVERAGE ( 'LearnerChart'[NearFailuresLC] ) ),
"AverageDistinctions", CALCULATE ( AVERAGE ( 'LearnerChart'[Distinctions] ) ),
"AverageAgeVsGradeInt", CALCULATE ( AVERAGE ( 'LearnerChart'[AgeVsGradeInt] ) )
)
EVALUATE
__DS0Core
The Parameter Cluster is defined to be a text input. Here is the query for the Cluster parameter.
EVALUATE
UNION (
SUMMARIZECOLUMNS (
System[Circuit],
System[Cluster]
),
{ ( "!All", "All" ) }
)
ORDER BY System[Cluster]
Query for Circuit Parameter
EVALUATE
UNION (
SUMMARIZECOLUMNS (
System[District],
System[Circuit]
),
{ ( "!All", "All" ) }
)
ORDER BY System[Circuit]
