topic Re: Need help in resolving DAX Query Issues in DAX Commands and Tips

Need help in resolving DAX Query Issues

ranjanrkl127 — Tue, 18 Aug 2020 14:19:34 GMT

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]
)

Re: Need help in resolving DAX Query Issues

Pragati11 — Tue, 18 Aug 2020 14:36:11 GMT

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

Re: Need help in resolving DAX Query Issues

ranjanrkl127 — Tue, 18 Aug 2020 14:53:04 GMT

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] )

Re: Need help in resolving DAX Query Issues

Pragati11 — Tue, 18 Aug 2020 14:58:01 GMT

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

Re: Need help in resolving DAX Query Issues

ranjanrkl127 — Tue, 18 Aug 2020 15:04:23 GMT

Sure, i will try now but wanted to check if MAX should really be used with String data? as the column has 3 string data and doing a MAX on it doesn't feel right, any thoughts?

Regards,

Ranjan

Re: Need help in resolving DAX Query Issues

AntrikshSharma — Tue, 18 Aug 2020 15:07:54 GMT

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 Result

Re: Need help in resolving DAX Query Issues

Pragati11 — Tue, 18 Aug 2020 15:07:12 GMT

Hi ranjanrkl127 ,

Therefore I mentioned that try creating a column rather than a measure, using your original DAX.

Also, try tagging people on your responses, just helps us.

Thanks,

Pragati

Re: Need help in resolving DAX Query Issues

ranjanrkl127 — Tue, 18 Aug 2020 15:32:09 GMT

Thanks 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 Result

Re: Need help in resolving DAX Query Issues

ranjanrkl127 — Tue, 18 Aug 2020 15:34:23 GMT

Thanks Pragati11,

I tried with just MAX but same error and I will need this to be a measure as per the requirement, can't create a column.

Re: Need help in resolving DAX Query Issues

AntrikshSharma — Tue, 18 Aug 2020 16:44:33 GMT

ranjanrkl127 What are the definitions of [TrackedSalesNetBase] and [Tracking Rate] ?

Re: Need help in resolving DAX Query Issues

Anonymous — Wed, 19 Aug 2020 10:45:46 GMT

This piece of code will never work:

VAR SummarizedTable =
IF (
IsModelTypeLeases,
SUMMARIZE ( 'Tracked Sales', Dates[Date], Leases[FactLease Key] ),
SUMMARIZE ( 'Tracked Sales', Dates[Date], Villages[Village Key] )
)

You CANNOT return a table from IF.