Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MacBI
Regular Visitor

Sytnax Error Rabbit Hole

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]

4 REPLIES 4
AntrikshSharma
Community Champion
Community Champion

@MacBI You are trying to access columns from a Variable which isn't supported.

 

Circuits[Seq] = MINX ( CircuitsCircuits[Seq] ) 

 

The Circuits[Seq] isn't a valid syntax.

Hmm, I'm not sure I agree with you. In my variable Circuits I have this defined.

VAR Circuits =
UNION (
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'System'[Circuit] ),
__FilterDistrict,
'System'[Circuit] <> BLANK ()
),
"Circuits[Seq]", 2
),
FILTER ( { ( @Circuit, 1 ) }, [Value1] <> "All" )
)

 

 

As mentioned above, I renamed my variable "Clusters" to "Cluxter" and everything compiled. My paginated reports are now working as intended.

@MacBI You're right. I used DAX Formatter it assumed that those were variable refrerences so I relied on it, didn't notice you used the Table[Column] notation in the new column name.

AntrikshSharma_0-1682672051758.png

The best practice while naming columns is to use "@" so that readers can easily distinguish between Model Column, Virtual Column and a Measure.

AntrikshSharma_1-1682672383463.png

 

 

Got it. I will start doing that going forward, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors