How to concatenate multiple columns value to use in a measure

[jamuka]

Hello all,

Sorry for subject but I don't know how to summarize my question.

I have Power BI Pro license. I have a sales report which has 53M rows, size of the .pbix file is 1.2GB. At the moment I can't publish it.

To reduce the size of it, I used VertiPaq Analyzer and splitted the biggest column (Invoice ID) to three columns as

Invoice ID.1, Invoice ID.2 and Invoice ID.3

Invoice ID.1's data type is text and others are whole number.

Now my file's size is 0.8GB. so I can publish it.

But I have several measures which uses Invoice ID column and I have to update my measures.

I assume I should use summarize or summarizecolumns but have no idea how to use them.

for example, below measures are for counting distinct Invoice IDs and summarizing Invoice Amount.

How should I update them?

Total Invoice = 
CALCULATE(
DISTINCTCOUNT('Sales'[Invoice ID])

Invoice Amount =
SUMX (
    VALUES ( 'Sales'[Invoice ID]  ),
    CALCULATE ( SELECTEDVALUE ( 'Sales'[Sales Amount] ) )

kind regards

[Fowmy · Accepted solution]

@jamuka 

Total Invoice = 
COUNTROWS(
    SUMMARIZE(Sales ,Sales[Invoice ID.1] ,Sales[Invoice ID.2] ,Sales[Invoice ID.3] )
)

Invoice Amount =
SUMX (
    SUMMARIZE(Sales ,Sales[Invoice ID.1] ,Sales[Invoice ID.2] ,Sales[Invoice ID.3] ),
    CALCULATE ( SUM(Sales[Sales Amount] ) )
)

[jamuka · Accepted solution]

Dear @Fowmy,

I figured out how to update my calculated column based on your answers. thanks for your help.

for calculated column I used

Unique Category Count = 
IF(Sales[Category Count] = 1,
CALCULATE(DISTINCTCOUNT(Sales[Category]),
ALLEXCEPT( Sales, Sales[Invoice ID.1],Sales[Invoice ID.2],Sales[Invoice ID.3])),0)

and for my measure

Uniqe Category Invoices = 
CALCULATE(
   DISTINCTCOUNT(Sales[Category]),
   ALLEXCEPT(Sales, Sales[Invoice ID.1],Sales[Invoice ID.2],Sales[Invoice ID.3],'Date'[Year Month]))

[Fowmy · Accepted solution]

@jamuka 

Total Invoice = 
COUNTROWS(
    SUMMARIZE(Sales ,Sales[Invoice ID.1] ,Sales[Invoice ID.2] ,Sales[Invoice ID.3] )
)

Invoice Amount =
SUMX (
    SUMMARIZE(Sales ,Sales[Invoice ID.1] ,Sales[Invoice ID.2] ,Sales[Invoice ID.3] ),
    CALCULATE ( SUM(Sales[Sales Amount] ) )
)

[jamuka]

Dear @Fowmy 

sorry for late reply. I updated my measures and it worked but I have several measures where I use ALLEXCEPT but I couldn't make my formula work.

Below you can find one of the measures. I also have a question about your first formula, instead of DISTINCTCOUNT you used COUNTROWS, are they same? In my report Invoice ID is not unique, so I thought I should use DISTINCTCOUNT.  But COUNTROWS work too.

Unique Category Count = 
IF(Sales[Category Count] = 1,
CALCULATE(
DISTINCTCOUNT(Sales[Category]),
ALLEXCEPT(Sales,Sales[Invoice ID])),0
)

regards

[Fowmy]

@jamuka 

You can modify the measure as follows:

Unique Category Count =
IF (
    Sales[Category Count] = 1,
    CALCULATE (
        DISTINCTCOUNT ( Sales[Category] ),
        SUMMARIZE (
            Sales,
            Sales[Invoice ID.1],
            Sales[Invoice ID.2],
            Sales[Invoice ID.3]
        ),
        REMOVEFILTERS ( Sales )
    )
)

Regarding your question, CONTROWS can be used to count a distinct column or table, I used SUMMARIZE which get the distinct combination of the invoice numbers from three columns and it counts. 

[jamuka]

thanks, I forgot that we didn't create a real column but a virtual one.

I also realize I made a mistake in my last reply, the formula I wrote is for a calculated column, when I create a column with your formula it shows same value in all cells. 

Also I have formulas where there are two columns and RemoveFilters didn't work with two columns, I get an message says

"Multiple table arguments are not allowed in the ALL/ALLNOBLANKROW/REMOVEFILTERS function."

Uniqe Category Invoices = 
CALCULATE(
   DISTINCTCOUNT(Sales[Category]),
   ALLEXCEPT(Sales,Sales[Invoice ID],'Date'[Year Month])
   )

I'm sorry for the confusion.