Forum Discussion

ad_nan's avatar
ad_nan
Regular Visitor
9 years ago
Solved

dimension in calculated measure

Hi,

 

Can we use dimension in calculated measures?

 

Also while creating a cal. measure i got an error : 

 

A single value for column 'dimension_name' in table 'table_name' 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.

 

Can anyone help?

 

Thanks,

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    According to the error that you are getting and that you are doing this in a measure, then you need to use an aggregator like:

     

    Measure = if(MAX('table'[dim_name]) = "dim1",((if(MAX('table'[dim_name]) = "dim2",'table'[measure]))/(if(MAX('table'[dim_name]) = "dim3",'table'[measure])))/12,5)

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Yes you can but you have to follow DAX syntax. Can you post your formula and sample data and what you are trying to accomplish?

    • ad_nan's avatar
      ad_nan
      Regular Visitor
      9 years ago

      Greg_Deckler

      Thanks for quick response.

       

      I am trying to implement something similar to this:

       

      if('table'[dim_name] = "dim1",((if('table'[dim_name] = "dim2",'table'[measure]))/(if('table'[dim_name] = "dim3",'table'[measure])))/12,5)

       

      '/' does not work here. 

       

      Thanks.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        9 years ago

        According to the error that you are getting and that you are doing this in a measure, then you need to use an aggregator like:

         

        Measure = if(MAX('table'[dim_name]) = "dim1",((if(MAX('table'[dim_name]) = "dim2",'table'[measure]))/(if(MAX('table'[dim_name]) = "dim3",'table'[measure])))/12,5)
  • Ivric's avatar
    Ivric
    Frequent Visitor
    8 years ago

    I'm trying to do some calcualtion by creating a new Measure in a dimension named Programs on power bi desktop

    if 'Programs'[StartDate] is blank then pick 'Programs'[AltDate] else pick 'Programs'[StartDate] 

     

    but in the measure i created under Programs I could access the date columns in my DAX.

     

    I'm connecting power bi live to ssas tabular cube.

     

     

    Thanks,

  • Ivric's avatar
    Ivric
    Frequent Visitor
    8 years ago

    I'm trying to do some calcualtion by creating a new Measure in a dimension named Programs on power bi desktop

    if 'Programs'[StartDate] is blank then pick 'Programs'[AltDate] else pick 'Programs'[StartDate] 

     

    but in the measure i created under Programs I could access the date columns in my DAX.

     

    I'm connecting power bi live to ssas tabular cube.

     

     

    Thanks,