Concatenation and calculation at high granularity

[ChPetru]

Hello,

I have the below table structure, where blue is fact and I need to calculate three additional columns.

One of them I managed to calculate, but I can't find a way to calculate the other two (which are actually interlinked.

Can you please help?

Distinct Cat Concatenation	CONCATENATEX(FILTER(SUMMARIZE(Table1,Table1[Deal],Table1[Cat]),Table1[Deal]=EARLIER(Table1[Deal])), Table1[Cat]," | ")
Cat Qty	need help, tried CALCULATE(SUM(Qty),Table1[Deal]=EARLIER(Table1[Deal]),(Table1[Deal])), Table1[Cat]) and it does not work
Distinct Cat & Qty Concatenation	need help

[v-jinweili-msft · Accepted solution]

Hi @ChPetru ,

• For Cat Qty column, yes,as you mentioned, it should be  'Table1'[Deal] = EARLIER ( 'Table1'[Deal] )&& 'Table1'[Cat] = EARLIER ( Table1[Cat])

          I have modified my original reply, please check.

• For slowly running,actually I could not reproduce the issue , all syntax worked fine on my side, but you could use Performance Analyzer to find out how each of your report elements, such as visuals and DAX formulas, are performing. Or try to upgrade your Power BI Desktop to the latest version or clean the cache.

Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jinweili-msft]

Hi @ChPetru 

According to your description, you want:

• Cat Qty column: Calculate sum of Qty for each Cat and Deal
• Calculate Distinct Cat Concatenation column: Combine distinct Cat for each Deal
• Calculate Distinct Cat & Qty Concatenation column: Combine distinct Cat and Cat Qty for each Deal

Please try:

Cat Qty =
CALCULATE (
    SUM ( Table1[Qty] ),
    FILTER (
        'Table1',
        'Table1'[Deal] = EARLIER ( 'Table1'[Deal] )
            && 'Table1'[Cat] = EARLIER ( Table1[Cat] )
    )
)

Distinct Cat Concatenation =
CONCATENATEX (
    FILTER (
        SUMMARIZE ( Table1, Table1[Deal], Table1[Cat] ),
        Table1[Deal] = EARLIER ( Table1[Deal] )
    ),
    Table1[Cat],
    " | "
)

Distinct Cat & Qty Concatenation =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table1', [Deal], [Cat], [Cat Qty] ),
        "Combine",
            [Cat] & " " & [Cat Qty]
    )
RETURN
    CONCATENATEX ( _t, [Combine], " | " )

Output:

Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChPetru]

Hello Jinwei,

Qty works if i swtich the first Table1'[Cat] = EARLIER ( 'Table1'[Cat]  to Table1'[Deal] = EARLIER ( 'Table1'[Deal].

Distinct Cat Concatenation also works.

Distinct Cat & Qty Concatenation however seems to work (no error) but runs indefinately and I have to end task (I actually waited 1h on a fairly small dataset, and still no result).

[amitchandak]

@ChPetru , try new measures like

CALCULATE(SUM(Qty),filter(Table, Table1[Deal]=EARLIER(Table1[Deal])),Table1[Deal])

or

CALCULATE(SUM(Qty),filter(Table, Table1[Deal]=EARLIER(Table1[Deal]) && Table1[Cat] =earlier(Table1[Cat]) ),Table1[Deal])