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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
newpbiuser01
Helper IV
Helper IV

Group Data to Summarize Table in DAX to Concatenate Values

Hello,

I have the following table where the Previous Month-Year column is a calculated column. and I'm trying to summarize the table by each vendor and the month-year.

VendorMonth-YearTypePrevious Month-Year
A2-2021Debit Card1-2021
A2-2021Credit Card1-2021
B3-2021Debit Card2-2021
C5-2021Credit Card4-2021
D1-2022Credit Card12-2021
E2-2022Check1-2022
B9-2023Check8-2023
C7-2021Credit Card6-2021
D11-2022Debit Card10-2022
A8-2023Check7-2023
B12-2022Credit Card11-2022
C7-2023Debit Card6-2023
D8-2021Check7-2021
B1-2023Debit Card12-2022

 

I tried doing it as a calculated column to get a concatenated list for the current month based on the month-year column and then another calculated column to get a concatenated list for the previous month based on the previous month-year column as follows:

 

Current Month Payment Type = 
    CALCULATE(CONCATENATEX(VALUES( 'Table'[Type]),'Table'[Type], ","), FILTER('Table', AND([Vendor] = EARLIER([Vendor]), [Month-Year] = EARLIER([Month-Year]))))
 
Previous Month Payment Type = 
    CALCULATE(CONCATENATEX(VALUES( 'Table'[Type]),'Table'[Type], ","), FILTER('Table', AND([Vendor] = EARLIER([Vendor]), [Month-Year] = EARLIER([Previous Month-Year]))))
 
However, I keep getting a circular dependency error. When I try doing it as a measure as follows:
 
Payment Type - Previous Month  = 
    CALCULATE(CONCATENATEX(VALUES( 'Table'[Type]),'Table'[Type], ","), FILTER('Table', [Month-Year] = [Previous Month-Year]))
 
I get a blank. What am I doing wrong? My goal is to have a table that shows
 

Vendor

Month-Year

Previous Month-Year

Current Month Payment Type

Previous Month Payment Type

A

2-2021

1-2021

Debit Card, Credit Card

 

B

3-2021

2-2021

Debit Card

Debit Card, Credit Card

C

5-2021

4-2021

Credit Card

 

D

1-2022

12-2021

Credit Card

 

E

2-2022

1-2022

Check

Credit Card

B

9-2023

8-2023

Check

Check

C

7-2021

6-2021

Credit Card

 

D

11-2022

10-2022

Debit Card

 

A

8-2023

7-2023

Check

Debit Card

B

12-2022

11-2022

Credit Card

Debit Card

 

I appreciate any help!

 

1 REPLY 1
ryan_mayu
Super User
Super User

@newpbiuser01 

is this what you want?

Table 2 = 
var tbl=SUMMARIZE('Table','Table'[Vendor],'Table'[Month-Year],"current type",CONCATENATEX('Table',[Type],","),"previous",EDATE('Table'[Month-Year],-1))
var tbl2=ADDCOLUMNS(tbl,"previous type",maxx(FILTER(tbl,'Table'[Vendor]=EARLIER('Table'[Vendor])&&'Table'[Month-Year]=EARLIER([previous])),[current type]))
return tbl2

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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