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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jamuka
Advocate II
Advocate II

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
Advocate II
Advocate II

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
Advocate II
Advocate II

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.