Need help with DAX - Incorrect syntax

[Redraidas1]

I have a very simple DAX snippet, however, for some reason Power BI throws an error saying:

'The syntax for ')' is incorrect.  

I can't figure out what's wrong?

count =
CALCULATE (
    DISTINCTCOUNT ( DimFranchiseMarket[FranchiseMarketName] ),
    FILTER (
        DimFranchiseMarket,
        SUMX (
            RELATEDTABLE ( GroupValuesOutput ),
            GroupValuesOutput[GroupValue] <> BLANK
        )
    )
)

[littlemojopuppy · Accepted solution]

The problem is with the SUMX statement.  Try this.

count =
CALCULATE (
	DISTINCTCOUNT ( DimFranchiseMarket[FranchiseMarketName] ),
	FILTER (
		DimFranchiseMarket,
		SUMX (
			RELATEDTABLE ( GroupValuesOutput ),
			GroupValuesOutput[GroupValue]
		) <> BLANK
	)
)

[ReznorTonya]

I too am struggling find an issue with a syntax error with Dax I've exhausted myself trying to figure it out 😞 ideas anyone???

ReadinessRating = VAR col1 = SUM(Readiness[Flag_StaffHired]) VAR col2 = SUM(Readiness[Flag_Hired and Deployable step 2]) VAR col3 = SUM(Readiness[Flag_Provider]) Var Col1_Stat = SWITCH( TRUE(), col1 >= 16, "S1", col1 >= 12, "S2", col1 >= 10, "S3", "S4") Var Col2_Stat = SWITCH( TRUE(), col2 >= 16, "S1", col2 >= 12, "S2", col2 >= 10, "S3", "S4") Var Col3_Stat = SWITCH( TRUE(), col3 >= 4, "S1", col3 = 3, "S2", col3 = 2, "S3", "S4") Return SWITCH( TRUE(), Col1_Stat = "S4" || Col2_Stat = "S4" || Col3_Stat = "S4", "S4", Col1_Stat = "S3" || Col2_Stat = "S3" || Col3_Stat = "S3", "S3", Col1_Stat = "S2" || Col2_Stat = "S2" || Col3_Stat = "S2", "S2", "S1")

[v-yiruan-msft]

Hi @ReznorTonya ,

You can open a new case for your problem in the community, and you will get a dedicated support. For your problem, you can use DAX Formatter tool to format your formula first. It will be easier to find the error. Please update the formula of [] as below and check if it can get your expected result. If no, you can raise a new case with the formula and the function which you want to achieve by this formula.

ReadinessRating =
VAR col1 =
    SUM ( Readiness[Flag_StaffHired] )
VAR col2 =
    SUM ( Readiness[Flag_Hired and Deployable step 2] )
VAR col3 =
    SUM ( Readiness[Flag_Provider] )
RETURN
    SWITCH (
        TRUE (),
        col1 >= 16
            || col2 >= 16
            || col3 >= 4, "S1",
        col1 >= 12
            || col2 >= 12
            || col3 = 3, "S2",
        col1 >= 10
            || col2 >= 10
            || col3 = 2, "S3",
        "S4"
    )

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @Redraidas1 ,

First, you can use DAX Formatter tool to format and validate your formula of measure. It will be more simple to find the problem. If it is OK after validate in the tool, then check the formula base on the actual requirement again. Anyway, @littlemojopuppy has already provided you the correct formula...

count =
CALCULATE (
    DISTINCTCOUNT ( DimFranchiseMarket[FranchiseMarketName] ),
    FILTER (
        DimFranchiseMarket,
        SUMX ( RELATEDTABLE ( GroupValuesOutput ), GroupValuesOutput[GroupValue] ) <> BLANK
    )
)

Best Regards

[littlemojopuppy · Accepted solution]

The problem is with the SUMX statement.  Try this.

count =
CALCULATE (
	DISTINCTCOUNT ( DimFranchiseMarket[FranchiseMarketName] ),
	FILTER (
		DimFranchiseMarket,
		SUMX (
			RELATEDTABLE ( GroupValuesOutput ),
			GroupValuesOutput[GroupValue]
		) <> BLANK
	)
)