topic Re: DAX for calculating based on one to many relationship in DAX Commands and Tips

DAX for calculating based on one to many relationship

vissvess — Sun, 28 Jul 2019 14:13:04 GMT

Hi,

Consider the following scenario.

My first table has the following type of data.

Sale ID	ProcessID	Timestamp
12345	Process 1a	8:23:30 AM
12345	Process 1b	8:24:46 AM
12345	Process 2a	9:32:20 AM
12345	Process 2b	9:36:43 AM
98765	Process 1a	8:56:10 AM
98765	Process 1b	8:57:56 AM
98765	Process 2a	9:45:45 AM
98765	Process 2b	9:46:55 AM

and so on...

And my second table is just the reference table with one single column created by removing duplicates on the saleID after removing other columns. Automatic relationship created between saleID columns on both the table.

Sale ID	Process 1	Process 2
12345	01:16	04:23
98765	01:46	01:10
38249	01:23	01:33
98452	01:53	02:43

So, I need a DAX expression to populate the second table Process 1 & Process 2 columns based on formula below.

Process 1 for SaleID 12345 = Process1b - Process1a &

Process 2 for SaleID 12345 = Process2b - Process2a and similar for other saleID as well.

I tried a using CALCULATE. But returns error.

Kindly someone help me with this.

Thanks in advance

Re: DAX for calculating based on one to many relationship

Anonymous — Sun, 28 Jul 2019 15:35:18 GMT

There should be a 1:many relationship from T to D, where T is your fact table and D is the dimension.

-- <x> should be replaced with 1 or 2

[Process <x>] = -- calculated column
var __proc1b =
	CALCULATE(
                -- This should return only 1 value. If more than 1
                -- value's returned, you'll get an error and
                -- that signals data quality problems in your data set.
		VALUES ( T[Timestamp] ), 
		T[ProcessID] = "Process <x>b"
	)
var __proc1a =
	CALCULATE( -- same remark as above
		VALUES ( T[Timestamp] ),
		T[ProcessID] = "Process <x>a"
	)
var __duration
	__proc1b - __proc1a
return
	__duration

Best

Darek

Re: DAX for calculating based on one to many relationship

vissvess — Sun, 28 Jul 2019 16:21:43 GMT

Hi,

Yes. But, there is a 1:many relationship from D to T (not T to D), where T is your fact table and D is the dimension.

Because Dimension table contains the unique values from Fact table. Fact tables have saleID in many rows. But one time in Dimension table. Thus, there is a 1:many relationship from D to T (not T to D).

Is this sounds correct??

Thanks

Re: DAX for calculating based on one to many relationship

vissvess — Sun, 28 Jul 2019 17:38:13 GMT

Anonymous , Also,

The following code results this error - "A table of multiple values was supplied where a single value was expected".

Process time =

var thirtytime =

CALCULATE(VALUES ( Archive[Time] ), Archive[Unit Status] = 30)

var twentytime =

CALCULATE(VALUES ( Archive[Time] ), Archive[Unit Status] = 20)

var __duration = thirtytime - twentytime

return

__duration

I am struck with somewhere here.

Thanks

Re: DAX for calculating based on one to many relationship

Anonymous — Sun, 28 Jul 2019 20:41:47 GMT

When you get an error saying:

"A table of multiple values was supplied where a single value was expected".

it means that one of your VALUES( ... ) returns more than 1 value. This means either you have a problem with data quality or you have not anticipated all possibilities, or your assumptions about the model are downright wrong.

Best

Darek

Re: DAX for calculating based on one to many relationship

vissvess — Mon, 29 Jul 2019 01:24:44 GMT

Hi Anonymous ,

Thanks for the insight. I have analysed my dataset thoroughly.

Yes, say There may be some duplicate entry in the data set as follows. For saleID 12345 process1a has two timestamps.

Sale ID	ProcessID	Timestamp
12345	Process 1a	8:03:24 AM
12345	Process 1a	8:23:30 AM
12345	Process 1b	8:24:46 AM
12345	Process 2a	9:32:20 AM
12345	Process 2b	9:36:43 AM
98765	Process 1a	8:56:10 AM
98765	Process 1b	8:57:56 AM
98765	Process 2a	9:45:45 AM
98765	Process 2b	9:46:55 AM

In this case the later time shall be taken for calculation process time for process 1 = 8:24:46 AM - 8:23:30 AM.

I tried to modify your code with MAX/MIN which also throws error - "The MAX function only accepts a column reference as an argument."

Best regards

Vissves

Re: DAX for calculating based on one to many relationship

vissvess — Mon, 29 Jul 2019 03:43:43 GMT

Anonymous , requesting your input for solving the above issue as well as could you please clarify regarding the one to many relationship controversy that I have raised in message 3 of 6 in this post.

Thanks

Re: DAX for calculating based on one to many relationship

Anonymous — Mon, 29 Jul 2019 08:32:33 GMT

[Process <x>] = -- calculated column
var __proc1b =
	CALCULATE(
 		MAX ( T[Timestamp] ), 
		T[ProcessID] = "Process <x>b"
	)
var __proc1a =
	CALCULATE( -- same remark as above
		MAX ( T[Timestamp] ),
		T[ProcessID] = "Process <x>a"
	)
var __duration =
	__proc1b - __proc1a
return
	__duration

There's always a 1:many relationship from a DIMENSION to a FACT. Always. So, D - 1:* - F. If I messed this up in the previous posts, it's because I was in a hurry.

Hope this clarifies everything.

Best

Darek