Forum Discussion

ranjanrkl127's avatar
ranjanrkl127
New Member
6 years ago

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 '...
DAX Calculation
Dax commands help
DAX Query
AntrikshSharma's avatar
AntrikshSharma
Community Champion
6 years ago

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's avatar
ranjanrkl127
New Member
6 years ago

Thanks 

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

 

 

 

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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.