Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to reference a column of a temporary table

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

Assuming the following sample data

Start MinuteEnd MinuteEstateStatus
01.12.2022 12:0101.12.2022 12:05CustomerASuccessful
01.12.2022 12:0201.12.2022 12:05CustomerBSuccessful
01.12.2022 12:0301.12.2022 12:07CustomerASuccessful
01.12.2022 12:0301.12.2022 12:03CustomerAFailed
01.12.2022 12:0301.12.2022 12:04CustomerBFailed
01.12.2022 12:0301.12.2022 12:09CustomerBSuccessful
01.12.2022 12:0501.12.2022 12:06CustomerBSuccessful
01.12.2022 12:0601.12.2022 12:09CustomerASuccessful

 

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 😞

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Assuming the following sample data

Start MinuteEnd MinuteEstateStatus
01.12.2022 12:0101.12.2022 12:05CustomerASuccessful
01.12.2022 12:0201.12.2022 12:05CustomerBSuccessful
01.12.2022 12:0301.12.2022 12:07CustomerASuccessful
01.12.2022 12:0301.12.2022 12:03CustomerAFailed
01.12.2022 12:0301.12.2022 12:04CustomerBFailed
01.12.2022 12:0301.12.2022 12:09CustomerBSuccessful
01.12.2022 12:0501.12.2022 12:06CustomerBSuccessful
01.12.2022 12:0601.12.2022 12:09CustomerASuccessful

 

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 😞

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.