Need help in resolving DAX Query Issues

[ranjanrkl127]

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

[AntrikshSharma]

@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

[ranjanrkl127]

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

[Anonymous]

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.

[AntrikshSharma]

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

[Pragati11]

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

[ranjanrkl127]

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

[Pragati11]

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]

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

[Pragati11]

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

[ranjanrkl127]

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.