Forum Discussion

KristyP's avatar
KristyP
Helper I
3 years ago
Solved

DAX Calucation

Hi,

 

I have two related tables. I am trying to find a value in one table based on a range ( min & max) and a category from the first table. The value I require is in the second table, seperate column for the category and the min & max, the value I require is in a seperate column again.

 

i.e

I have a series of categories, with products that fit into the different categories. Each product has a different (and known) amount of usage. The related table contains a list of products and value based on usage (the value is based on a range e.g 2400 - 7200, there are several different ranges depending on category and usage).

18 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi KristyP 
    I might be better if you start with a sample data of both tables, indication the relationship if (exist) along with the expected result.

  • KristyP's avatar
    KristyP
    Helper I
    3 years ago

    I have provided very basic form of the data I have to work with.

     

     

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      KristyP 
      Please try

      Value =
MAXX (
    FILTER (
        Table2,
        Table2[Product Group] = Table1[Product Group]
            && Table2[Min Usage Range] <= Table1[Usage]
            && Table2[Max Usage Range] >= Table1[Usage]
    ),
    Table2[Value]
)
      • KristyP's avatar
        KristyP
        Helper I
        3 years ago

        My apologies, I misunderstood.

         

        I have tried this, but getting this error:

        DAX expression operations do not support comparing values of type text with values of type integer. Consider the VALUE or FORMAT function to convert one of the values.

         

        I have changed the Usage and value to text for the moment, which worked. Just concerned how this affect other calculation moving forward. Is there a better way to do this?

  • KristyP's avatar
    KristyP
    Helper I
    3 years ago

    Thank you,

     

    I have tried your solution, but am getting the followinf error:

    A single value for column 'Product Group' in table 1 cannot be determined. This can happen when a measure refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      KristyP 
      This is supposed to be a calculated column in Table1 not a measure.

      • KristyP's avatar
        KristyP
        Helper I
        3 years ago

        I haved used this calculation. But there are some issues with it not always picking up the correct value. This issue does not seem relate to any one particular Product Goup or band. Any ideas what could be casuing this issue?

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

    first make the measure of total sales.
    then create one table which include all ranges according to your range you want.

    then use this measure:

    rangevalues = Calculate([totalsale measure name],
    VAR currentrange=

    FILTER(

    table1,table2,

    AND(tablename[value]<=range tablename,

              tablename[value]>=range tablename))

    RETURN

      rangevalue
    ) 

    • KristyP's avatar
      KristyP
      Helper I
      3 years ago

      With reference to the total sales measure, which column from which table are you referring to?