Forum Discussion

rbreneman's avatar
rbreneman
Helper II
3 years ago
Solved

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...
Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    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!

rbreneman's avatar
rbreneman
Helper II
3 years ago

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."

 

vicky_'s avatar
vicky_
Super User
3 years ago

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!

  • rbreneman's avatar
    rbreneman
    Helper II
    3 years ago

    Thank you!!!! This was so helpful! I was still missing the future months where I didn't have any data in my revenue transaction table. I was able to make a small change to what you provided and it's now working perfect. Basically added another column for count of committed status. I then filtered by in progress > 0 OR committed is blank.

     

    MonthCloseCard = 
VAR Helper = ADDCOLUMNS(VALUES(DateTbl[Month Year]), "In Progress Count", CALCULATE(COUNTROWS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "In Progress"))), "Committed Count", CALCULATE(COUNTROWS(FILTER('Revenue Transactions','Revenue Transactions'[Status] = "Committed"))) )
VAR Months = FILTER(Helper, [In Progress Count] > 0 || [Committed Count] = BLANK() )
VAR Solution = CONCATENATEX(Months, [Month Year], ", " )
Return
Solution

     

    Thanks again! I appreciate you taking the time to help me to get to this point.

    • vicky_'s avatar
      vicky_
      Super User
      3 years ago

      I'm glad you got it working!