Need some help with DAX

[venkata]

I am getting below error.

Error Message:
MdxScript(Model) (11, 118) Calculation error in measure 'StationList'[Measure]: Function 'SWITCH' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I am using below formulae:

Measure = VAR SelectedGross = CALCULATE ([StnGross], INTERSECT ( VALUES ( Gross[STATION] ), VALUES ( StationList[STATION] ) ))
VAR UnSelectedGross = CALCULATE ( [StnGross], EXCEPT (ALL ( Gross[STATION] ), VALUES ( StationList[STATION] ) ) )
VAR AllGross = CALCULATE ( [StnGross], ALL ( 'Gross'[STATION] ) )
RETURN IF ( HASONEVALUE ( StationList[STATION] ), SWITCH ( VALUES ( StationList[STATION] ), "OTHER", SelectedGross , UnSelectedGross, AllGross))

[v-yulgu-msft]

Hi @venkata,

It looks like that the SWITCH function in above measure formula doesn't meet the correct syntax.

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

The <expression> should return a single scalar value, where the expression is to be evaluated multiple times (for each row/context). However, in your scenario, VALUES ( StationList[STATION] ) returns a one-column table that contains the distinct values.

Also, your purpose is that if result of <expression> equals "OTHER", then return SelectedGross, but how about UnSelectedGross and AllGross? What are their corresponding values?

Regards,

Yuliana Gu

[mattbrice]

Is StationList{STATION] a number or text?  Either way, it is trying to compare it to "OTHER" (which is text) or to 'UnSelectedGross' which i assume is an integer.  This is not allowed.  All comparisons must be one or the other.

[venkata]

It is a text

[venkata]

Error Message:
MdxScript(Model) (11, 118) Calculation error in measure 'StationList'[Measure]: Function 'SWITCH' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

Power BI Formulas:

section Section1;

shared Gross = let
Source = Excel.Workbook(File.Contents("C:\Users\mani\Desktop\MS Power BI (2018)\Station Summarized.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"STATION", type text}, {"BUDGET", type number}, {"GROSS", type number}})
in
#"Changed Type";

Formlae I have used: 

Measure = VAR SelectedGross = CALCULATE ([StnGross], INTERSECT ( VALUES ( Gross[STATION] ), VALUES ( StationList[STATION] ) ))
VAR UnSelectedGross = CALCULATE ( [StnGross], EXCEPT (ALL ( Gross[STATION] ), VALUES ( StationList[STATION] ) ) )
VAR AllGross = CALCULATE ( [StnGross], ALL ( 'Gross'[STATION] ) )
RETURN IF ( HASONEVALUE ( StationList[STATION] ), SWITCH ( VALUES ( StationList[STATION] ), "OTHER", SelectedGross , UnSelectedGross, AllGross))