topic Re: Dax Union - To Add a Static Column in DAX Commands and Tips

Dax Union - To Add a Static Column

Suneer — Tue, 05 Dec 2023 04:57:12 GMT

I've generated a Union Query in DAX and included a static field named "MainAccount" (highlighted in red). This functionality functions correctly in Power BI. However, when I attempt to replicate the same in a paginated report, an error is encountered. The provided DAX query serves as an example. Assistance in resolving this issue would be greatly appreciated.

SampleTable1 = Union(
selectcolumns(SampleTable1,
"Company", SampleTable1[Company],
"Invoice Date", SampleTable1[InvoiceDate]
"MainAccount", "SalesAccount"),
selectcolumns(SampleTable2,
"Company", SampleTable2[Company],
"Invoice Date", SampleTable2[InvoiceDate],
"MainAccount", "CostAccount"))

Re: Dax Union - To Add a Static Column

some_bih — Mon, 04 Dec 2023 14:52:15 GMT

Hi Suneer

for both "SalesAccount" and "CostAccount" you will need table reference (usually)

Re: Dax Union - To Add a Static Column

Suneer — Tue, 05 Dec 2023 04:56:24 GMT

I observed that when incorporating a static field in a DAX query within a paginated report, it should be positioned last. (see the mistake I did in the following example. The example i shown in the original post was correct ) . In my situation, I mistakenly positioned the static field between other fields.

Eg.

SampleTable1 = Union(
selectcolumns(SampleTable1,
"MainAccount", "SalesAccount",
"Company", SampleTable1[Company],
"Invoice Date", SampleTable1[InvoiceDate])
,
selectcolumns(SampleTable2,
"MainAccount", "CostAccount",
"Company", SampleTable2[Company],
"Invoice Date", SampleTable2[InvoiceDate]))

Re: Dax Union - To Add a Static Column

some_bih — Tue, 05 Dec 2023 07:12:49 GMT

Hi Suneer UNION "wants" proper column structure for underlying columns.

Check link for this functions https://learn.microsoft.com/en-us/dax/union-function-dax?WT.mc_id=DP-MVP-4025372