TEXTJOIN with an OFFSET using DAX

[Anonymous]

Hi all,

I'm having some difficulty trying to replicate a formula used in excel within a calculated column in Power BI

The formula within excel in cell c2 of the sample data below is 
=TEXTJOIN(",",TRUE, OFFSET(B2,0,0,COUNTIF(A:A,A2),1))

Essentially what it does is, in the sample data attached, it looks through the ID column. The ID in row 10 appears only once, and it returns only FY2022-Q1 (from the Quarter column).

If the same ID appears four times (see rows 2-5),
in cell C2, it will join FY2022 - Q1, FY2022 - Q2, FY2022 - Q3, FY2022 - Q4,
in cell C3, it will join FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,

in cell C4, it will join FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,
in cell C5, it will join FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3

Any help replicating this formula would be greatly appreciated. 

ID	QUARTER	Expected Result
5001002	FY2022 - Q1	FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4
5001002	FY2022 - Q2	FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1
5001002	FY2022 - Q3	FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2
5001002	FY2022 - Q4	FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3
5001003	FY2022 - Q1	FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4
5001003	FY2022 - Q2	FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1
5001003	FY2022 - Q3	FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q1
5001003	FY2022 - Q4	FY2022 - Q4,FY2022 - Q1,FY2022 - Q1,FY2022 - Q1
5001004	FY2022 - Q1	FY2022 - Q1
5001006	FY2022 - Q1	FY2022 - Q1
5001007	FY2022 - Q1	FY2022 - Q1
5001008	FY2022 - Q1	FY2022 - Q1
5001009	FY2022 - Q1	FY2022 - Q1
5001010	FY2022 - Q1	FY2022 - Q1
5001011	FY2022 - Q1	FY2022 - Q1
5001012	FY2022 - Q1	FY2022 - Q1
5001013	FY2022 - Q1	FY2022 - Q1
5001014	FY2022 - Q1	FY2022 - Q1
5001015	FY2022 - Q1	FY2022 - Q1
5001016	FY2022 - Q1	FY2022 - Q1
5001017	FY2022 - Q1	FY2022 - Q1
5001018	FY2022 - Q1	FY2022 - Q1
5001019	FY2022 - Q1	FY2022 - Q1
5001020	FY2022 - Q1	FY2022 - Q1
5001021	FY2022 - Q3	FY2022 - Q3
5001022	FY2022 - Q2	FY2022 - Q2
5001023	FY2022 - Q2	FY2022 - Q2
5001024	FY2022 - Q1	FY2022 - Q1
5001026	FY2022 - Q1	FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4
5001026	FY2022 - Q2	FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1
5001026	FY2022 - Q3	FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2
5001026	FY2022 - Q4	FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3
5001027	FY2022 - Q1	FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4
5001027	FY2022 - Q2	FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q4
5001027	FY2022 - Q3	FY2022 - Q3,FY2022 - Q4,FY2022 - Q4,FY2022 - Q1
5001027	FY2022 - Q4	FY2022 - Q4,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2
5001028	FY2022 - Q4	FY2022 - Q4
5001029	FY2022 - Q1	FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4
5001029	FY2022 - Q2	FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1
5001029	FY2022 - Q3	FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q1
5001029	FY2022 - Q4	FY2022 - Q4,FY2022 - Q1,FY2022 - Q1,FY2022 - Q1

[Fowmy · Accepted solution]

@Anonymous 

First, add an index column in Power Query then add the following column using DAX in your data model

Column = 
VAR __IND = Table1[Index] RETURN
CONCATENATEX(
    CALCULATETABLE(
        VALUES(Table1[QUARTER]),
        Table1[Index] >= __IND && Table1[Index] < __IND + 4,
        REMOVEFILTERS(Table1)
    ),Table1[QUARTER],", ",Table1[QUARTER]
)

File attached below

[Anonymous]

Hi Fowmy,

Thank you for your reply,

When I use that formula in my file, for some reason I only get a return of the same value as is in the Quarter column, without it being joined with the previous quarters. Do you know what may be impacting this?

[Anonymous]

Never mind, it was my mistake. Your formula worked perfectly. Thank you so much!

[wdx223_Daniel]

[Anonymous]

Hi wdx223_Daniel,

Thank you for your response.

I have tried your solution as well and I get the same result as I do with Fowmy's above where the calculated column returns the same result as is in the quarter column, without joining the previous/future quarters. I'm not sure what could be messing with it.