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!

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!