Can we get a list of items (text) under different month headings , in power bi?

Question

The requirement that I have is like this.

There is a dataset of some item names. Each item have a completion date. Now I need to show in a good looking visual, the list of items under each month.

Most of the visuals in Power BI try to aggregate the field pulled into it, and hence it will try to show "First" or "Last" of the items since "Item" is a text. Visuals like matrix can only show the count of items against each month.

I tried aggregating the text using Concatenation, and tried to pull the Items into a custom visual "Multi Info Cards" , which gave the best solution so far, but the items were showing up as joined into one line, though I used delimiter as "UNICHAR(10)". Hence could not proceed.

The other way was using "Table" and having a card visual on top of it, to show the heading as month. But that would mean, I keep multiple tables for each month, and have some visual filters for each table to decide which month to show. But this is not a flexible model.

Can this requirement be achieved in power bi? Please help with your valuable suggestions. Please click here to see what the expected output should look like

Answer 1

You can use the CONCATENATEX DAX formula to accomplish this. Create a new measure like this:

items_list = 
CONCATENATEX(
        items,
        items[item],
        ", ",
        items[item],
        ASC
)

Then, using a matrix visual and Power BI's automatic date hierarchy, you can show monthly values like this:

It gets a little more complicated if you want to remove the duplicate values. Here's how I was able to do it

1. Create a calculated month column in the items table

   month = DATE(YEAR(items[completion date]),MONTH(items[completion date]),1)

2. Create a calculated table for unique item and month combinations

   distinct_items = DISTINCT(SELECTCOLUMNS(items,"item",items[item],"month",items[month]))

3. create a measure in the distinct_items table using CONCATENATEX

   distinct_items_list = CONCATENATEX( distinct_items, distinct_items[item], ", ", distinct_items[item],ASC )

Here is the resulting matrix visual:

Edit: Here are some more display options using the distinct_items table created above. The multi row card and matrix visuals will be much easier to use and not require any manual filtering. By using a card visual and a multi row card combined, I was able to recreate something similar to you requirements, but you will need to manually filter each element for a specific month (or incorporate this into your dax formulas)