topic Looking for ideas on UNIONX or DISTINCTX in DAX Commands and Tips

Looking for ideas on UNIONX or DISTINCTX

lbendlin — Fri, 24 Nov 2023 20:55:49 GMT

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)

Re: Looking for ideas on UNIONX or DISTINCTX

jpessoa8 — Fri, 24 Nov 2023 19:54:58 GMT

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

Re: Looking for ideas on UNIONX or DISTINCTX

lbendlin — Fri, 24 Nov 2023 20:57:43 GMT

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.

Re: Looking for ideas on UNIONX or DISTINCTX

jpessoa8 — Sat, 25 Nov 2023 14:59:22 GMT

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

Re: Looking for ideas on UNIONX or DISTINCTX

lbendlin — Sat, 25 Nov 2023 18:22:31 GMT

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

Re: Looking for ideas on UNIONX or DISTINCTX

lbendlin — Mon, 27 Nov 2023 15:59:03 GMT

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