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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
jamuka
Helper IV
Helper IV

How to concatenate multiple columns value to use in a measure

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

2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@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] ) )
)



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

jamuka
Helper IV
Helper IV

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]))

View solution in original post

5 REPLIES 5
jamuka
Helper IV
Helper IV

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
Super User
Super User

@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] ) )
)



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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. 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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