topic Re: TEXTJOIN with an OFFSET using DAX in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128615#M48841 <P>Anonymous</LI-USER>&nbsp;<BR /><BR />First, add an index column in Power Query then add the following column using DAX in your data model<BR /><BR /></P> <P>&nbsp;</P> <LI-CODE lang="markup">Column = VAR __IND = Table1[Index] RETURN CONCATENATEX( CALCULATETABLE( VALUES(Table1[QUARTER]), Table1[Index] &gt;= __IND &amp;&amp; Table1[Index] &lt; __IND + 4, REMOVEFILTERS(Table1) ),Table1[QUARTER],", ",Table1[QUARTER] )</LI-CODE> <P><img /></P> <P><BR />File attached below</P> <P>&nbsp;</P> <P>&nbsp;</P> Tue, 12 Oct 2021 04:49:46 GMT Fowmy 2021-10-12T04:49:46Z TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128421#M48833 <P>Hi all,</P><P>&nbsp;</P><P>I'm having some difficulty trying to replicate a formula used in excel within a calculated column in Power BI<BR /><BR />The formula within excel in cell c2 of the sample data below is&nbsp;<BR />=TEXTJOIN(",",TRUE, OFFSET(B2,0,0,COUNTIF(A:A,A2),1))</P><P>&nbsp;</P><P>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).<BR /><BR />If the same ID appears four times (see rows 2-5),<BR />in cell C2, it will join FY2022 - Q1, FY2022 - Q2, FY2022 - Q3, FY2022 - Q4,<BR />in cell C3, it will join&nbsp;FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,</P><P>in cell C4, it will join FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,<BR />in cell C5, it will join&nbsp;FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3<BR /><BR />Any help replicating this formula would be greatly appreciated.&nbsp;<BR /><BR /></P><TABLE><TBODY><TR><TD>ID</TD><TD>QUARTER</TD><TD>Expected Result</TD></TR><TR><TD>5001002</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4</TD></TR><TR><TD>5001002</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1</TD></TR><TR><TD>5001002</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2</TD></TR><TR><TD>5001002</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3</TD></TR><TR><TD>5001003</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4</TD></TR><TR><TD>5001003</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1</TD></TR><TR><TD>5001003</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q1</TD></TR><TR><TD>5001003</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4,FY2022 - Q1,FY2022 - Q1,FY2022 - Q1</TD></TR><TR><TD>5001004</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001006</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001007</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001008</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001009</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001010</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001011</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001012</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001013</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001014</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001015</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001016</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001017</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001018</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001019</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001020</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001021</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3</TD></TR><TR><TD>5001022</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2</TD></TR><TR><TD>5001023</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2</TD></TR><TR><TD>5001024</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1</TD></TR><TR><TD>5001026</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4</TD></TR><TR><TD>5001026</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1</TD></TR><TR><TD>5001026</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2</TD></TR><TR><TD>5001026</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4,FY2022 - Q1,FY2022 - Q2,FY2022 - Q3</TD></TR><TR><TD>5001027</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4</TD></TR><TR><TD>5001027</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q4</TD></TR><TR><TD>5001027</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3,FY2022 - Q4,FY2022 - Q4,FY2022 - Q1</TD></TR><TR><TD>5001027</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4,FY2022 - Q4,FY2022 - Q1,FY2022 - Q2</TD></TR><TR><TD>5001028</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4</TD></TR><TR><TD>5001029</TD><TD>FY2022 - Q1</TD><TD>FY2022 - Q1,FY2022 - Q2,FY2022 - Q3,FY2022 - Q4</TD></TR><TR><TD>5001029</TD><TD>FY2022 - Q2</TD><TD>FY2022 - Q2,FY2022 - Q3,FY2022 - Q4,FY2022 - Q1</TD></TR><TR><TD>5001029</TD><TD>FY2022 - Q3</TD><TD>FY2022 - Q3,FY2022 - Q4,FY2022 - Q1,FY2022 - Q1</TD></TR><TR><TD>5001029</TD><TD>FY2022 - Q4</TD><TD>FY2022 - Q4,FY2022 - Q1,FY2022 - Q1,FY2022 - Q1</TD></TR></TBODY></TABLE><P>&nbsp;</P> Tue, 12 Oct 2021 02:59:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128421#M48833 Anonymous 2021-10-12T02:59:50Z Re: TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128607#M48840 <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> <P>&nbsp;</P> Tue, 12 Oct 2021 07:44:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128607#M48840 wdx223_Daniel 2021-10-12T07:44:27Z Re: TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128615#M48841 <P>Anonymous</LI-USER>&nbsp;<BR /><BR />First, add an index column in Power Query then add the following column using DAX in your data model<BR /><BR /></P> <P>&nbsp;</P> <LI-CODE lang="markup">Column = VAR __IND = Table1[Index] RETURN CONCATENATEX( CALCULATETABLE( VALUES(Table1[QUARTER]), Table1[Index] &gt;= __IND &amp;&amp; Table1[Index] &lt; __IND + 4, REMOVEFILTERS(Table1) ),Table1[QUARTER],", ",Table1[QUARTER] )</LI-CODE> <P><img /></P> <P><BR />File attached below</P> <P>&nbsp;</P> <P>&nbsp;</P> Tue, 12 Oct 2021 04:49:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128615#M48841 Fowmy 2021-10-12T04:49:46Z Re: TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128736#M48845 <P>Hi Fowmy,</P><P>&nbsp;</P><P>Thank you for your reply,</P><P>&nbsp;</P><P>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?<BR /><BR /></P> Tue, 12 Oct 2021 06:09:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128736#M48845 Anonymous 2021-10-12T06:09:59Z Re: TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128740#M48847 <P>Hi wdx223_Daniel,</P><P>&nbsp;</P><P>Thank you for your response.</P><P>&nbsp;</P><P>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.</P> Tue, 12 Oct 2021 06:12:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128740#M48847 Anonymous 2021-10-12T06:12:18Z Re: TEXTJOIN with an OFFSET using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128756#M48848 <P>Never mind, it was my mistake. Your formula worked perfectly. Thank you so much!</P> Tue, 12 Oct 2021 06:22:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TEXTJOIN-with-an-OFFSET-using-DAX/m-p/2128756#M48848 Anonymous 2021-10-12T06:22:22Z