topic Re: DAX Calucation in DAX Commands and Tips

DAX Calucation

KristyP — Mon, 29 May 2023 07:42:41 GMT

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

Re: DAX Calucation

tamerj1 — Mon, 29 May 2023 08:05:02 GMT

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.

Re: DAX Calucation

KristyP — Tue, 30 May 2023 00:18:54 GMT

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

Re: DAX Calucation

tamerj1 — Tue, 30 May 2023 06:57:30 GMT

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

Re: DAX Calucation

KristyP — Tue, 30 May 2023 07:42:36 GMT

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.

Re: DAX Calucation

tamerj1 — Tue, 30 May 2023 08:26:03 GMT

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

Re: DAX Calucation

devanshi — Tue, 30 May 2023 12:19:24 GMT

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
)

Re: DAX Calucation

KristyP — Wed, 31 May 2023 01:18:31 GMT

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?

Re: DAX Calucation

KristyP — Mon, 12 Jun 2023 03:39:29 GMT

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

Re: DAX Calucation

KristyP — Mon, 12 Jun 2023 07:16:16 GMT

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?

Re: DAX Calucation

tamerj1 — Mon, 12 Jun 2023 07:33:54 GMT

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

Re: DAX Calucation

KristyP — Mon, 12 Jun 2023 07:37:14 GMT

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

Re: DAX Calucation

KristyP — Tue, 13 Jun 2023 04:40:15 GMT

I have done this very quickly, please see below

I made sure there was a relationship between the two tables

Re: DAX Calucation

tamerj1 — Tue, 13 Jun 2023 05:19:18 GMT

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

Re: DAX Calucation

KristyP — Tue, 13 Jun 2023 05:34:39 GMT

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

Not sure if this will work

Re: DAX Calucation

tamerj1 — Tue, 13 Jun 2023 07:20:04 GMT

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.

Re: DAX Calucation

KristyP — Wed, 14 Jun 2023 01:04:58 GMT

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.

Re: DAX Calucation

tamerj1 — Wed, 14 Jun 2023 10:37:56 GMT

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

Re: DAX Calucation

KristyP — Thu, 15 Jun 2023 00:34:24 GMT

Sure, I can do this tomorrow.