Looking for ideas on UNIONX or DISTINCTX

[lbendlin]

I am still looking for ways to work around the missing UNIONX command in DAX - the ability to append an arbitrary number of table variables or more precisely lists (single column table variables) inside a table variable.

Here is the Power Query equivalent:

let
    Source = #table({"Start","End"},{{2,3},{3,4},{6,7}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {[Start]..[End]})
in
    List.Distinct(List.Combine(#"Added Custom"[Custom]))

The main premise is to handle more than two overlapping intervals without duplication of the sample points (EDIT: while also accounting for gaps). It is extremely easy to do in Power Query as per the above, and it is reasonably easy to do graphically. But the numerical solution eludes me.

(I have raised an idea for UNIONX back in 2021 and it has so far roared to a single vote - mine. Microsoft Idea)  

[lbendlin]

Thank you, that approach works. Testing at scale now.

[lbendlin]

Here's a slightly different setup but the technical result is the same

UNIONX_NUMERIC = 
DISTINCT(
    SELECTCOLUMNS(
        GENERATE(
		SUMMARIZECOLUMNS(NumericTable[Start],NumericTable[End]),
		GENERATESERIES([Start],[End])
	),"Value",[Value]
    )
)

[jpessoa8]

Hi @lbendlin ,

I took a look at the Microsoft Idea you've provided since I was having a hard time understanding the logic of the dataset in this topic.

Considering the dates scenario on the idea page, isn't this a possible solution in DAX : 

EVALUATE
SELECTCOLUMNS(
	GENERATE(
		VALUES('Table'[Start]),
		VAR EndDate = CALCULATE(MAX('Table'[End]))
		RETURN
			CALENDAR(
				'Table'[Start],
				EndDate
			)
	),
	"Date",
	[Date]
)

With this dataset :

This is the result I've obtained:

Here is the PowerBI file I've used : https://we.tl/t-9kiA3jSR8z

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[lbendlin]

@jpessoa8  I will have to be more precise in my request.  Let's assume I have  an arbitrary number of lists.  For this exercise three lists will suffice

List 1:  from 2 to 3

List 2: from 3 to 4

List 3: from 6 to 7

Now I want to find the distinct values for the combination of all these lists, and the result should be

2,3,4,6,7

So the clarification will be that there will be overlaps between the lists but also gaps.   Meaning that your Min/Max approach is not applicable, unfortunately.

[jpessoa8 · Accepted solution]

@lbendlin,

I think you dind't look correctly to the outcome of the DAX expression. The MAX measures is just to get the corresping "end" list value for each "start" value. It will be the same if I used SELECTEDVALUE().

Using your example of numeric lists, but with numbers even further apart:

List1 : from 1 to 5

List2: from 10 to 14

List3: from 20 to 35

The new calculated table DAX Expression :

UNIOX_NUMERIC = 
DISTINCT(
	SELECTCOLUMNS(
		GENERATE(
			VALUES('NumericTable'[Start]),
			VAR EndValue = CALCULATE(MAX('NumericTable'[End]))
			RETURN
				GENERATESERIES(
					'NumericTable'[Start],
					EndValue
				)
		),
		"Value",
		[Value]
	)
)

With this I got the following list of numbers:

1,2,3,4,5,10,11,12,13,14,20,21,222,23,24,25,26,27,28,29,30,31,32,33,34,35

Isn't this what you were looking for?

Here is the PowerBI file with both scenarios : https://we.tl/t-zNZ6WSSUSB

For additional help, please @ me in your reply!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa