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
mb_etn
Regular Visitor

Creating a Column/Measure for List Length

I am new to Power BI this week.  I have imported data (from Jira), and I have one column (customfield_10100) which is a List.  I do not care about the list contents, just the list length.  I am hoping to create a new column/measure which will be a basic integer (the length of the list).  I then plan to bucket these counts and put them on a chart or graph (how many 1 lengths, how many 2 lengths, etc.).

 

What is the best way to do this?

4 REPLIES 4
mb_etn
Regular Visitor

Anyone have any thoughts?  I have come to the conclusion that DAX doesn't support interacting with the 'List' datatype, but correct me if I am wrong.

Greg_Deckler
Super User
Super User

Depends on what you mean by "length". I am assuming you are referring to how many items are in the list. If that is the case, use my special trickiness for this assuming that your items are separated by something like a comma or something:

 

Column = LEN([List]) - LEN(SUBSTITUTE(List[List],",","")) + 1

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Ok, I believe I figured out your equation, though it won't do what I need.  If I understand correctly, your equation assumes the Cell contents are a string.  It counts the number of total characters, counts the number of non-comma characters, then takes the difference and adds 1; essentially it counts commas+1 (or a delimiter of your choosing) to get the list elements.

 

Unfortunately, this column does not store the data as strings formatted as a list; they are the 'List' type.  When looking at the data in "Query Editor", the field contents show up as a yellow "List"; you can select the cell, and a mini-table appears at the bottom showing the list contents as their own column.

 

My problem is that I cannot find any documentation on how to interact with this object.  Both 'List' and 'Record' appear in my imported data set, but Power BI does not even reference these in their official Data Type documentation even though it clearly is handling them uniquely!

 

Calculating a simple length here doesn't seem too crazy.  There is a function called COUNTROWS which counts the rows in a table.  As a 'List' shows up as a mini-table, I would think that executing COUNTROWS on this column should do what I want, but this gives me the error "The COUNTROWS function expects a table expression for argument '1', but a string or numeric expression was used.".

 

There has to be a way to do this...

Thanks for the reply.  You are correct that I only need how many items are in the list.  

 

Looking at your example, I am not certain where to insert my field name; for instance, what replaces "[List]" and "List[List]".  As some more information, my column is called 'customfield_10100' and the table is 'GetIssues', often invoked as "GetIssues[customfield_10100]".  How would that fit into your schema.

 

I'm not certain this exact solution will work for me as this particular list is a complex structure under the surface (might have sub-lists under it), and I need only the count of the outer list structure.  Still, once I get a better understanding of your solution, I might be able to work something out.

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.