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
Pragati11's avatar
Pragati11
Super User
6 years ago

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

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's avatar
    Pragati11
    Super User
    6 years ago

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

      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's avatar
        Pragati11
        Super User
        6 years ago

        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