DAX Calucation

[KristyP]

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

[devanshi]

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]

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

[KristyP]

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]

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

[KristyP]

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?

[tamerj1]

@KristyP 
Can you provide a sample file that clarifies these inconsistencies?

[KristyP]

I need to protect the information for work purposes. But I will complete a sample file tomorrow and send it through.

[KristyP]

I have done this very quickly, please see below

I made sure there was a relationship between the two tables

[tamerj1]

Hi @KristyP 
Would you please share a download link of this sample file?

[KristyP]

https://drive.google.com/file/d/1YRtPCnyg-m7PylEVKGYkIADYahU3amWB/view?usp=drive_link

Not sure if this will work

[tamerj1]

@KristyP 
The reason you're getting blanks is that the Names of the groups in the two tables do not match: One table says "Product A" and the other says "Product Group A" and so on. I created a new group column just to get itt work.

[KristyP]

Sorry, this is something I double checked in the actual one. The names are all correct. I can not find any consistency with the error. I will have 20 for one product gorup, 15 will read correct 1 will be blank and the rest reading incorrectly.

[tamerj1]

@KristyP 
Let's connect via teams or zoom if possible.

[KristyP · Accepted solution]

Sure, I can do this tomorrow.

[KristyP]

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

[tamerj1]

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

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?

[tamerj1]

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.