Forum Discussion
Help with concatenatex
Hi!
I have a column chart visual as shown below. X-axis is Month Year from my date table. The numbers on the y-axis come from another table called Revenue Transactions, see example of table below. I want to write a measure and display it in a card on my report that tells the viewer whether any of the shown months are still in-progress. Based on the example table data below it would look something like "The following month(s) have not yet been closed: March 2023". It would need to support the possibility of multiple months being in progress, so listed as comma separated. The user could also change a date
filter and see 6 months instead of a quarter (3 months) in the column chart.
I'd appreciate anyone that could share an example of some DAX that would do this. I know I'll want to use concatenatex but I'm struggling with the filters to make it all work as expected.
Thanks so much!
Revenue Transactions Table:
| Date | Revenue | Status |
| 1/1/2023 | 10000 | Committed |
| 1/7/2023 | 50000 | Committed |
| 2/1/2023 | 49000 | Committed |
| 3/1/2023 | 22000 | In Progress |
| 3/9/2023 | 2000 | Committed |
Date Table:
| Date | Month Year |
| 1/1/2023 | Jan 2023 |
| 1/7/2023 | Jan 2023 |
| 2/1/2023 | Feb 2023 |
| 3/1/2023 | Mar 2023 |
| 3/9/2023 | Mar 2023 |
In that case, what you can try is to add column to the date table instead of the other way around...
So something like this:
MonthCloseCard = VAR Helper = ADDCOLUMNS(VALUES('Date'[Month]), "In Progress Count", CALCULATE(COUNTROWS(FILTER(Data, Data[Status] = "In Progress")))) VAR Months = FILTER(Helper, [In Progress Count] > 0) VAR Solution = CONCATENATEX( Months, FORMAT('Date'[Month], "mmm yyyy "), ", " ) // return Solution Return "The following months have not yet been closed: " & Solution & ". Data may not be accurate."I hope this helps!
5 Replies
I used this measure, based on a variation of your provided data:
Measure = var helper = ADDCOLUMNS(FILTER('Table', 'Table'[Status] = "In Progress"), "Month", EOMONTH('Table'[Date], 0)) return CONCATENATEX( DISTINCT(SELECTCOLUMNS(helper, "distinct month", [Month])), FORMAT([distinct month], "mmm yyyy"), ", " )vicky_ ,
This is great, thank you so much!This is working great for the months that are in the Revenue Transactions table. Where this isn't working is the current month of July and anything in the future. For example, there currently aren't any transactions loaded in the table with a July 2023 date, however July 2023 is in my date table and therefor selectable by the user (this is by design). If I expand my date slicer to show the following columns: Apr 2023, May 2023, Jun 2023, Jul 2023, and Aug 2023 (screenshot below). The measure only outputs June 2023 but I would want it to include months out of the date table that are missing from the revenue transactions table.
Is it possible to somehow include the date table dimension in this measure to account for months missing from the revenue transactions table?
Thanks so much for your help on this!
MonthCloseCard = VAR Helper = ADDCOLUMNS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "In Progress"), "Month", EOMONTH('Revenue Transactions'[Date], 0)) VAR Months = DISTINCT(SELECTCOLUMNS(Helper, "Distinct Month", [Month])) VAR Solution = CONCATENATEX( Months, FORMAT([Distinct Month], "mmm yyyy"), ", " ) Return "The following months have not yet been closed: " & Solution & ". Data may not be accurate."In that case, what you can try is to add column to the date table instead of the other way around...
So something like this:
MonthCloseCard = VAR Helper = ADDCOLUMNS(VALUES('Date'[Month]), "In Progress Count", CALCULATE(COUNTROWS(FILTER(Data, Data[Status] = "In Progress")))) VAR Months = FILTER(Helper, [In Progress Count] > 0) VAR Solution = CONCATENATEX( Months, FORMAT('Date'[Month], "mmm yyyy "), ", " ) // return Solution Return "The following months have not yet been closed: " & Solution & ". Data may not be accurate."I hope this helps!
