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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rlannvercorp
New Member

Changing the filter context on iterations of CONCATENATEX

I have table of the following values:

Category1,Category2

A,1

A,2

A,3

A,4

B,1

B,3

B,4

C,2

C,4

 

I want a measure to return a string summarizing this table as such (order of values not important):

A (1, 2, 3, 4),
B (1, 3),

C (2, 4)

But all I've been able to come up with is 

Nested List of Values =
var category2list = CONCATENATEX(DISTINCT('Table'[Category2]), 'Table'[Category2], ", ")
return
CONCATENATEX( DISTINCT('Table'[Category1]),
'Table'[Category1] & " (" & category2list & ")", ", " & UNICHAR(10))

Which returns
A (1, 2, 3, 4),
B (1, 2, 3, 4),
C (1, 2, 3, 4) so the inner expression isn't being run on a table filtered for each iteration of the outer concatenatex.

I would also like to be able to use a filter on either category to return a filtered list like so:
Select Category2 = 4 returns
A (4),
C (4) 
or Category2 = 2 or 3 returns
A (2, 3),
B (3),
C (2)
1 ACCEPTED SOLUTION

@rlannvercorp ,

 

Try like

 

 

Measure = var _tab = SUMMARIZE('text' , 'text'[Category1], "_1", CONCATENATEX(DISTINCT('text'[Category2]), 'text'[Category2],",")) return CONCATENATEX( _tab,
[Category1] & " (" & [_1]& ")", ", " & UNICHAR(10))

 

Screenshot 2021-04-30 19.07.50.png

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

an alternative solution,

ConcX = 
CONCATENATEX(
    DISTINCT( 'text'[Category1] ),
    'text'[Category1] & ": "
        & CALCULATE( CONCATENATEX( DISTINCT( 'text'[Category2] ), 'text'[Category2], ", " ) ),
    UNICHAR( 10 )
)

Screenshot 2021-04-30 205542.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

amitchandak
Super User
Super User

@rlannvercorp ,

Try to get more than one row like

Nested List of Values =

var category2list = CONCATENATEX(DISTINCT('Table'[Category2]), 'Table'[Category2], ", ")

return

'Table'[Category1] & " (" & category2list & ")"

 

or

Nested List of Values =

var category2list = CONCATENATEX('Table', 'Table'[Category2], ", ")

return

'Table'[Category1] & " (" & category2list & ")"

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@rlannvercorp ,

 

Try like

 

 

Measure = var _tab = SUMMARIZE('text' , 'text'[Category1], "_1", CONCATENATEX(DISTINCT('text'[Category2]), 'text'[Category2],",")) return CONCATENATEX( _tab,
[Category1] & " (" & [_1]& ")", ", " & UNICHAR(10))

 

Screenshot 2021-04-30 19.07.50.png

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak  Now I understand.  Very elegant and simple.  Thanks for the help.

Thank you!  Now to figure out HOW it works.

DAX doesn't accept 'Table'[Category1] in the last line.  It's requiring a scalar value.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.