Summarize table and add new column

[Anonymous]

Hi!

I'm trying to create a New Table using DAX in PowerBI but I'm having trouble. I tried to create it using the following formula:

Table = 
ADDCOLUMNS(
    SUMMARIZE(
        ADDCOLUMNS(
            SUMMARIZE(
                'Sales Detail',
                [Customer Number],
                [Order Number],
                [SalesRep Name]
            ), 
        "Unique Value", INT(1)
        ),
    [Customer Number],
    [Order Number],
    [SalesRep Name]
    ),
"Total Filter", CALCULATE(SUM([Unique Value]))
)

I get the following error: Cannot identify the table that contains [Unique Value] colum (I tried other combinations, but this one is the best for the following solution that I came up and I don't understand well why this doesn't work)

If I create 2 tables, then I can create the calculated column that I'm looking for:

Table 1 = 

        ADDCOLUMNS(
            SUMMARIZE(
                'Sales Details',
                [Customer Number],
                [Order Number],
                [SalesRep Name]
            ), 
        "Unique Value", INT(1)
        )

Table 2 = 
ADDCOLUMNS(
    SUMMARIZE('Table 1',
    'Table 1'[Customer Number],
    'Table 1'[Order Number],
    'Table 1'[SalesRep Name]
    ),
"Total Filter",CALCULATE(SUM('Table 1'[Unique Value])))

The thing is, I don't want to create two tables because I'll end with twice as much as tables needed, and also I think this will make my file slower as more calculations are needed (I may be wrong with that assumption).

Any help will be kindly appreciated!

Thanks!

[edhans]

I believe you will need to create two tables. I cannot see how to get one of the outer tables to recognize a new column created by one of the inner calculated tables.

FWIW, you should look at SUMMARIZECOLUMNS instead of ADDCOLUMNS and SUMMARIZE. See this article. More robust and less calculating.

I'm also not clear on what you are expecting INT(1) to do besides return a 1 every time, which might as well be 1 and not INT(1), but it seems that your final SUM(UNIQUE) is just counting rows, in which this will do that.

Table =
VAR tmpTable =
    SUMMARIZE (
        Sales,
        Sales[Customer Number],
        Sales[Order Number],
        Sales[SalesRep Name]
    )
RETURN
    ADDCOLUMNS ( tmpTable, "Unique Values", COUNTROWS ( tmpTable ) )

[Anonymous]

Thank you very much for your reply!

I have created 2 tables but I want to do it in just one.

The first "query" is to select all 3 uniques columns and then add a column with value equal to 1, this is for checking that there are no more than one combination of Column 1 + Column 2 + Column 3.

The second table I'm re-grouping by the 3 columns and now I want to know if there is more than just one, that is why I'm adding all of these Unique Values. If there is a value greater than 1, I need to check the data.

Your approach counting rows may work too! But using your solution, I get the value count rows and not grouped by columns. (I think that if you specify the grouping variables it will work).

Nevertheless, I wanted to perform 2 queries in one table, so I don't have to be creating actual data tables everytime. Creating VAR I can't then select the column for a specific calculated column. If I want to use SUM it doesn't work, but using COUNTROWS works. I'm guessing that the table is "created" as variable but I don't know how to perform a second calculation on that variable

[Greg_Deckler]

See if you use VAR to create your two tables, you can do that all within a single table DAX formula. Then just RETURN your second table variable.

[edhans]

---

@Greg_Deckler wrote:

See if you use VAR to create your two tables, you can do that all within a single table DAX formula. Then just RETURN your second table variable.

---

I couldn't get the final calculated table to see new columns added in the VAR/RETURN. It said it could only access columns in a base table, not a calculated table. Do you have a code snippet that would do what you are showing?