Issues with tables using measures with SWITCH function when published to PBI Service (worked before)

[Anonymous]

Hi,

We're having problems with a report that contains tables with measures using a SWITCH function. This report worked fine with the old tables and matrixes both in PBI Desktop and PBI Service. But after releasing the new tables and matrixes into production (out of preview) the old tables stopped working. So, I switched to the new tables and after a bit of fiddling around (disabling totals which got turned on by default when changing the visual type to the new table) I got this working again in the latest version of PBI Desktop (version: 2.48.4792.721 64-bit (July 2017)), but when I publish it to the service it still isn't working.

Can you please look into why it would work in PBI Dekstop but not in PBI service and why it stopped working when switched to the new table.

This issue is preventing the use of some key reports for our organisation, so please look into this ASAP.

Many thanks,

Jure

The error message below:

Couldn't load the data for this visual

MdxScript(Model) (66, 4) Calculation error in measure 'Measures 1'[Actual]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Please try again later or contact support. If you contact support, please provide these details.

Activity IDf8c88fee-5641-40dd-bbb9-94e32d7f0719

Request ID0586797e-28ca-56c8-5e99-2b1b079a4322

Correlation ID18cfb1c8-5fd8-027e-ff14-f6e0467c6757

TimeThu Jul 27 2017 12:54:24 GMT+0200 (Romance Daylight Time)

Version13.0.1930.145

Cluster URIhttps://wabi-north-europe-redirect.analysis.windows.net

[v-qiuyu-msft]

Hi @Anonymous,

From the error message, the issue related to the 'Measures 1'[Actual]. Can you open the report in Power BI desktop, copy the DAX of this measure and share it here? So we can try to test it on our side.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi,

See below:

Actual =

VAR
vBrandCount = DISTINCTCOUNT('Brand Logos'[Brand Group])
VAR
vBrand = DISTINCT('Brand Logos'[Brand Group])
VAR
vMeasure = DISTINCT('Measures 1'[Measure])
VAR
vFormatPct = "#0.0%;(#0.0)%"
//VAR
// vFormatPP = "#0.0pp;(#0.0)pp"
VAR
vFormatNum = "#,##0;(#,##0)"
VAR
vFormatGBP = SWITCH(
TRUE()
,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000000
,"£#,##0,,,.0B;£(#,##0,,,.0)B"

,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000
,"£#,##0,,.0M;£(#,##0,,.0)M"

,ABS(SUM('MainData (Phased)'[Current - A]))>=1000
,"£#,##0,.0K;£(#,##0,.0)K"

,ABS(SUM('MainData (Phased)'[Current - A]))>=0
,"£#,##0;£(#,##0)"
)
VAR
vCalcPct = FORMAT(SUM('MainData (Phased)'[Current - Num A])/SUM('MainData (Phased)'[Current - Den A]),vFormatPct)
VAR
vCalcGBP = FORMAT(SUM('MainData (Phased)'[Current - A]),vFormatGBP)
VAR
vCalcActiveRMP = FORMAT(IF(DISTINCTCOUNT('Brand Logos'[Brand Group])>1,BLANK(),IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,sum('MainData (Phased)'[Current - A]),sum('BrandLevel (Phased)'[Current - A]))),vFormatNum)

RETURN

SWITCH(
TRUE()

,vMeasure="Turnover"
,vCalcGBP

,vMeasure="GGR"
,vCalcGBP

,vMeasure="GGR %"
,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="GGR %"),"GGR % - Actual",vCalcPct)

,vMeasure="Bonus Cost"
,vCalcGBP

,vMeasure="Bonus Cost %"
,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="Bonus Cost %"),"Bonus Cost % - Actual",vCalcPct)

,vMeasure="NGR"
,vCalcGBP

,vMeasure="NGR %"
,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="NGR %"),"NGR % - Actual",vCalcPct)

,vMeasure="FTDs"
,FORMAT(IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,BLANK(),sum('BrandLevel (Phased)'[Current - A])),vFormatNum)

,vMeasure="Actives"
,vCalcActiveRMP

,vMeasure="RMPs"
,vCalcActiveRMP

)

[Anonymous]

This works fine in Desktop and worked fine before July 2017 in the service as well.

[Anonymous]

Hi Qiuyun Yu,

Any update on this? We can't use some of our key reports without this issue being resolved.

Many thanks,

Jure

[v-qiuyu-msft]

Hi @Anonymous,

Please comment the SELECTCOLUMNS() part from the measure then publish to service to see if the issue related to SELECTCOLUMNS().

Actual =

VAR
vBrandCount = DISTINCTCOUNT('Brand Logos'[Brand Group])
VAR
vBrand = DISTINCT('Brand Logos'[Brand Group])
VAR
vMeasure = DISTINCT('Measures 1'[Measure])
VAR
vFormatPct = "#0.0%;(#0.0)%"
//VAR
// vFormatPP = "#0.0pp;(#0.0)pp"
VAR
vFormatNum = "#,##0;(#,##0)"
VAR
vFormatGBP = SWITCH(
TRUE()
,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000000
,"£#,##0,,,.0B;£(#,##0,,,.0)B"

,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000
,"£#,##0,,.0M;£(#,##0,,.0)M"

,ABS(SUM('MainData (Phased)'[Current - A]))>=1000
,"£#,##0,.0K;£(#,##0,.0)K"

,ABS(SUM('MainData (Phased)'[Current - A]))>=0
,"£#,##0;£(#,##0)"
)
VAR
vCalcPct = FORMAT(SUM('MainData (Phased)'[Current - Num A])/SUM('MainData (Phased)'[Current - Den A]),vFormatPct)
VAR
vCalcGBP = FORMAT(SUM('MainData (Phased)'[Current - A]),vFormatGBP)
VAR
vCalcActiveRMP = FORMAT(IF(DISTINCTCOUNT('Brand Logos'[Brand Group])>1,BLANK(),IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,sum('MainData (Phased)'[Current - A]),sum('BrandLevel (Phased)'[Current - A]))),vFormatNum)

RETURN

SWITCH(
TRUE()

,vMeasure="Turnover"
,vCalcGBP

,vMeasure="GGR"
,vCalcGBP

//,vMeasure="GGR %"
//,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="GGR %"),"GGR % - Actual",vCalcPct)

,vMeasure="Bonus Cost"
,vCalcGBP

//,vMeasure="Bonus Cost %"
//,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="Bonus Cost %"),"Bonus Cost % - Actual",vCalcPct)

,vMeasure="NGR"
,vCalcGBP

//,vMeasure="NGR %"
//,SELECTCOLUMNS(FILTER('Measures 1','Measures 1'[Measure]="NGR %"),"NGR % - Actual",vCalcPct)

,vMeasure="FTDs"
,FORMAT(IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,BLANK(),sum('BrandLevel (Phased)'[Current - A])),vFormatNum)

,vMeasure="Actives"
,vCalcActiveRMP

,vMeasure="RMPs"
,vCalcActiveRMP

)

Best Regards,
Qiuyun Yu

[Anonymous]

Hi Qiuyun Yu,

Thanks for your help, it looks like removing SELECTCOLUMNS() did the trick. I realised I don't actually need all that around my calc, so I just left the calc from the vCalcPct variable (it's weird this same thing both worked before and works fine in PBI Desktop):

Actual =
VAR
vBrandCount = DISTINCTCOUNT('Brand Logos'[Brand Group])
VAR
vBrand = DISTINCT('Brand Logos'[Brand Group])
VAR
vMeasure = DISTINCT('Measures 1'[Measure])
VAR
vFormatPct = "#0.0%;(#0.0)%"
//VAR
// vFormatPP = "#0.0pp;(#0.0)pp"
VAR
vFormatNum = "#,##0;(#,##0)"
VAR
vFormatGBP = SWITCH(
TRUE()
,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000000
,"£#,##0,,,.0B;£(#,##0,,,.0)B"
,ABS(SUM('MainData (Phased)'[Current - A]))>=1000000
,"£#,##0,,.0M;£(#,##0,,.0)M"
,ABS(SUM('MainData (Phased)'[Current - A]))>=1000
,"£#,##0,.0K;£(#,##0,.0)K"
,ABS(SUM('MainData (Phased)'[Current - A]))>=0
,"£#,##0;£(#,##0)"
)
VAR
vCalcPct = FORMAT(SUM('MainData (Phased)'[Current - Num A])/SUM('MainData (Phased)'[Current - Den A]),vFormatPct)
VAR
vCalcGBP = FORMAT(SUM('MainData (Phased)'[Current - A]),vFormatGBP)
VAR
vCalcActiveRMP = FORMAT(IF(DISTINCTCOUNT('Brand Logos'[Brand Group])>1,BLANK(),IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,sum('MainData (Phased)'[Current - A]),sum('BrandLevel (Phased)'[Current - A]))),vFormatNum)

RETURN
SWITCH(
TRUE()
,vMeasure="Turnover"
,vCalcGBP
,vMeasure="GGR"
,vCalcGBP
,vMeasure="GGR %"
,vCalcPct
,vMeasure="Bonus Cost"
,vCalcGBP
,vMeasure="Bonus Cost %"
,vCalcPct
,vMeasure="NGR"
,vCalcGBP
,vMeasure="NGR %"
,vCalcPct
,vMeasure="FTDs"
,FORMAT(IF(DISTINCTCOUNT('MainData (Phased)'[Product Vertical])=1,BLANK(),sum('BrandLevel (Phased)'[Current - A])),vFormatNum)
,vMeasure="Actives"
,vCalcActiveRMP
,vMeasure="RMPs"
,vCalcActiveRMP
)

[v-qiuyu-msft]

Hi @Anonymous,

SELECTCOLUMNS Function (DAX) returns a table instead of one value. In your scenario, perhaps the SELECTCOLUMNS() returns more than one row along with the updated data, then cause the issue. Please be careful using SELECTCOLUMNS() function.

Best Regards,
Qiuyun Yu