topic Re: How to reference a column of a temporary table in DAX Commands and Tips

How to reference a column of a temporary table

Anonymous — Thu, 29 Dec 2022 14:21:27 GMT

I try to create a table dynamically because if I create it in advance calculation never ends if I try to analyze a full year

I created a table and a calculated column which works in principle but I try to do it in one step

tab =
ADDCOLUMNS (
FILTER (
DISTINCT (
UNION (
DISTINCT ( Calls[Start Minute] ),
DISTINCT ( Calls[End Minute] )
)
),

// just to decrease amount of data
[Start Minute] >= DATE ( 2022, 12, 01 )
&& [Start Minute] <= DATE ( 2022, 12, 15 )
),
"CustomerA",
CALCULATE (
COUNTROWS ( Calls ),
FILTER (
Calls,
Calls[Start Minute] <= [Start Minute]
&& Calls[End Minute] >= [Start Minute]
&& Calls[Estate] = "DE_customerA"
&& Calls[Status] = "Successful"
)
) + 0
)

If I add the calculated column

CustomerA_OK =

CALCULATE (

COUNTROWS ( Calls),

FILTER (

Calls,

Calls[Start Minute] <= tab[Start Minute]

&& Calls[Start Minute] >= tab[Start Minute]

Calls[Estate] = "DE_CustomerA",

Calls[Status] = "Successful"

) + 0

Unfortunately I need this for several customers and if I add the columm for each of them my system is not able to process (works if I limit the rows of the calls

I get a different result for CustomerA_OK and CustomerA, seems like the column [Start Minute] doesn't work, but I have no idea who to use the correct value even within CALCULATE

The idea is to create a measure which calculates the maximum for a specific timespan and a specific customer.

any hints would be great

Re: How to reference a column of a temporary table

amitchandak — Thu, 29 Dec 2022 15:36:58 GMT

Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: How to reference a column of a temporary table

Anonymous — Thu, 29 Dec 2022 22:05:49 GMT

I think I found a solution (but I do not understand the difference)

Assuming the following sample data

Start Minute	End Minute	Estate	Status
01.12.2022 12:01	01.12.2022 12:05	CustomerA	Successful
01.12.2022 12:02	01.12.2022 12:05	CustomerB	Successful
01.12.2022 12:03	01.12.2022 12:07	CustomerA	Successful
01.12.2022 12:03	01.12.2022 12:03	CustomerA	Failed
01.12.2022 12:03	01.12.2022 12:04	CustomerB	Failed
01.12.2022 12:03	01.12.2022 12:09	CustomerB	Successful
01.12.2022 12:05	01.12.2022 12:06	CustomerB	Successful
01.12.2022 12:06	01.12.2022 12:09	CustomerA	Successful

with

tabNOK =

VAR mytab =

DISTINCT (

UNION ( DISTINCT ( Calls[Start Minute] ), DISTINCT ( Calls[End Minute] ) )

)

RETURN

ADDCOLUMNS (

mytab,

"CustomerA",

CALCULATE (

COUNTROWS ( Calls ),

FILTER (

Calls,

Calls[Start Minute] <= [Start Minute]

&& Calls[End Minute] >= [Start Minute]

&& Calls[Estate] = "CustomerA"

&& Calls[Status] = "Successful"

)

) + 0

)

my Column CustomerA has always the value 3

If I use

tabOK =

VAR mytab =

SELECTCOLUMNS (

DISTINCT (

UNION ( DISTINCT ( Calls[Start Minute] ), DISTINCT ( Calls[End Minute] ) )

"Timestamp", [Start Minute]

)

RETURN

ADDCOLUMNS (

mytab,

"CustomerA",

CALCULATE (

COUNTROWS ( Calls ),

FILTER (

Calls,

Calls[Start Minute] <= [Timestamp]

&& Calls[End Minute] >= [Timestamp]

&& Calls[Estate] = "CustomerA"

&& Calls[Status] = "Successful"

)

) + 0

)

So this seems to be related to the same column name 😞