How to reference a column of a temporary table

[Anonymous]

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

[amitchandak]

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

[Anonymous · Accepted solution]

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 😞