Forum Discussion
Need help in resolving DAX Query Issues
This is my DAX Query and It fails with the error below, the column [Modelling Types] has multiple values, any quick help in resolving this?
A single value for column 'Modelling Types' in table 'Modelling Types' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
IF (
SELECTEDVALUE ( 'Modelling Types'[Modelling Types] ) = "Multiply using Leases Tracking Rate"
|| SELECTEDVALUE ( 'Modelling Types'[Modelling Types] ) = "Multiply using Village Tracking Rate",
SUMX (
VAR SummarizedTable =
IF (
'Modelling Types'[Modelling Types] = "Multiply using Leases Tracking Rate",
SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ),
SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] )
)
RETURN
ADDCOLUMNS (
SummarizedTable,
"Tracking Rate", IF (
'Modelling Types'[Modelling Types] = "Multiply using Leases Tracking Rate",
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' )
),
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' )
)
),
"selectedmeasure", [TrackedSalesNetBase]
),
VAR MultiplicationFactor =
DIVIDE ( 1, [Tracking Rate] )
RETURN
[selectedmeasure] * MultiplicationFactor
),
[TrackedSalesNetBase]
)
10 Replies
Hi ranjanrkl127 ,
I think you are creating a MEASURE here. If you try creating a COLUMN using same DAX, you will not get the error.
If you still need this as a measure, then use an aggregation function around your Model Type column like below:
IF (
SELECTEDVALUE ( MAX('Modelling Types'[Modelling Types] )) = "Multiply using Leases Tracking Rate"
|| SELECTEDVALUE ( MAX('Modelling Types'[Modelling Types]) ) = "Multiply using Village Tracking Rate",
SUMX (
VAR SummarizedTable =
IF (
MAX('Modelling Types'[Modelling Types]) = "Multiply using Leases Tracking Rate",
SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ),
SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] )
)
RETURN
ADDCOLUMNS (
SummarizedTable,
"Tracking Rate", IF (
MAX('Modelling Types'[Modelling Types]) = "Multiply using Leases Tracking Rate",
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' )
),
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' )
)
),
"selectedmeasure", [TrackedSalesNetBase]
),
VAR MultiplicationFactor =
DIVIDE ( 1, [Tracking Rate] )
RETURN
[selectedmeasure] * MultiplicationFactor
),
[TrackedSalesNetBase]
)Thanks,
Pragati
Thanks Pragati,
Yes, I am creating a measure, I applied MAX as suggested but deployment failed again with the error: any further suggestion i can try?
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.IF ( SELECTEDVALUE(MAX( 'Modelling Types'[Modelling Types] )) = "Multiply using Leases Tracking Rate" || SELECTEDVALUE(MAX ( 'Modelling Types'[Modelling Types] )) = "Multiply using Village Tracking Rate", SUMX ( VAR SummarizedTable = IF ( MAX('Modelling Types'[Modelling Types]) = "Multiply using Leases Tracking Rate", SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ), SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] ) ) RETURN ADDCOLUMNS ( SummarizedTable, "Tracking Rate", IF ( MAX ( 'Modelling Types'[Modelling Types] ) = "Multiply using Leases Tracking Rate", CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' ) ), CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' ) ) ), "selectedmeasure", [TrackedSalesNetBase] ), VAR MultiplicationFactor = DIVIDE ( 1, [Tracking Rate] ) RETURN [selectedmeasure] * MultiplicationFactor ), [TrackedSalesNetBase] )Hi ranjanrkl127 ,
Remove SELECTED VALUE clause from your DAX. It should work.
IF (
( MAX('Modelling Types'[Modelling Types] )) = "Multiply using Leases Tracking Rate"
|| ( MAX('Modelling Types'[Modelling Types]) ) = "Multiply using Village Tracking Rate",
SUMX (
VAR SummarizedTable =
IF (
MAX('Modelling Types'[Modelling Types]) = "Multiply using Leases Tracking Rate",
SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ),
SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] )
)
RETURN
ADDCOLUMNS (
SummarizedTable,
"Tracking Rate", IF (
MAX('Modelling Types'[Modelling Types]) = "Multiply using Leases Tracking Rate",
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' )
),
CALCULATE (
[% Tracked Sales vs Total Sales],
ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' )
)
),
"selectedmeasure", [TrackedSalesNetBase]
),
VAR MultiplicationFactor =
DIVIDE ( 1, [Tracking Rate] )
RETURN
[selectedmeasure] * MultiplicationFactor
),
[TrackedSalesNetBase]
)I can't test the DAX at my end as I don't have data. But yes try the above chnaged dax.
Thanks,
Pragati
ranjanrkl127 Use variables so that you can check intermediate steps by replacing the Result variable at the end.
= VAR ModelType = SELECTEDVALUE ( 'Modelling Types'[Modelling Types] ) VAR IsModelTypeLeases = ModelType = "Multiply using Leases Tracking Rate" VAR IsModelTypeVillage = ModelType = "Multiply using Village Tracking Rate" VAR SummarizedTable = IF ( IsModelTypeLeases, SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ), SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] ) ) VAR IntermediateTable = ADDCOLUMNS ( SummarizedTable, "Tracking Rate", IF ( IsModelTypeLeases, CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' ) ), CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' ) ) ), "selectedmeasure", [TrackedSalesNetBase] ) VAR PreResult = SUMX ( IntermediateTable, VAR MultiplicationFactor = DIVIDE ( 1, [Tracking Rate] ) RETURN [selectedmeasure] * MultiplicationFactor ) VAR Result = IF ( IsModelTypeLeases || IsModelTypeVillage, PreResult, [TrackedSalesNetBase] ) RETURN ResultThanks AntrikshSharma,
I tried the code with variable, but deployment still fails with the error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.VAR ModelType = SELECTEDVALUE ( 'Modelling Types'[Modelling Types] ) VAR IsModelTypeLeases = ModelType = "Multiply using Leases Tracking Rate" VAR IsModelTypeVillage = ModelType = "Multiply using Village Tracking Rate" VAR SummarizedTable = IF ( IsModelTypeLeases, SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ), SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] ) ) VAR IntermediateTable = ADDCOLUMNS ( SummarizedTable, "Tracking Rate", IF ( IsModelTypeLeases, CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Leases' ) ), CALCULATE ( [% Tracked Sales vs Total Sales], ALLEXCEPT ( 'Tracked Sales', 'Dates', 'Villages' ) ) ), "selectedmeasure", [TrackedSalesNetBase] ) VAR PreResult = SUMX ( IntermediateTable, VAR MultiplicationFactor = DIVIDE ( 1, [Tracking Rate] ) RETURN [selectedmeasure] * MultiplicationFactor ) VAR Result = IF ( IsModelTypeLeases || IsModelTypeVillage, PreResult, [TrackedSalesNetBase] ) RETURN Resultranjanrkl127 What are the definitions of [TrackedSalesNetBase] and [Tracking Rate] ?
