cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to create to column series (One duplicates the row values of the other while one stays 'unique')

Good morning,

 

I have the following code 

Start of Month Dates = 
GENERATE(
    GENERATESERIES(1,3),
    Var yr=YEAR(TODAY())
    Var mo=MONTH(TODAY())
    Var inc= mo + [Value]
    Return Row (
        "Date",Date(yr,inc,1)
    )
)    

and I obtain the following table: 

Value | Date

1        | 7/1/2019:00:00 AM

2        | 8/1/2019:00:00 AM

 

I want to add another column for another, lets call it value2 and that it goes from 1 to 3, but I want the original value and date duplicated.  After each disctinct Value 2 has finished, it will restart but with the following value of the Value column and Date. The final table would look like this: 

 

Value | Date                         | Value2

1        | 7/1/2019:00:00 AM  |  1

1        | 7/1/2019:00:00 AM  |  2

1        | 7/1/2019:00:00 AM  |  3

2        | 8/1/2019:00:00 AM  |  1

2        | 8/1/2019:00:00 AM  |  2

2        | 8/1/2019:00:00 AM  |  3

 

 

I tried something similar to this code:

Start of Month Dates = 
GENERATE(
    GENERATESERIES(1,3),
    Var yr=YEAR(TODAY())
    Var mo=MONTH(TODAY())
    Var inc= mo + [Value]
     Var Value2 = [Value]
    Return Row (
        "Date",Date(yr,inc,1),
         "Value2", Value2
    )
)    

and I got this table (Which is not what I want) :

 

Value | Date                        | Value2

1        | 7/1/2019:00:00 AM | 1

2        | 8/1/2019:00:00 AM | 2

3        | 9/1/2019:00:00 AM | 3 

 

I also tried this code but the the error of 'Too many arguments were passed to the GENERATE function. The maximum argument count for the function is 2'.

 

Start of Month Dates = 
GENERATE(
   GENERATE( GENERATESERIES(1,2), GENERATESERIES(1,3)),
    Var yr=YEAR(TODAY())
    Var mo=MONTH(TODAY())
    Var inc= mo + [Value]
     Var Value2 = [Value2]
    Return Row (
        "Date",Date(yr,inc,1),
         "Value2", Value2
    )
)    

Regards,

 

YC

2 REPLIES 2
AUaero
Advocate II
Advocate II

Seems like you should be able to do this by crossjoining another table.

EVALUATE
var Table1 = 
GENERATE(
    GENERATESERIES(1,3),
    Var yr=YEAR(TODAY())
    Var mo=MONTH(TODAY())
    Var inc= mo + [Value]
    RETURN 
    Row ("Date",Date(yr,inc,1))
)    

Var Table2 = 
DATATABLE(
	"Value2", Integer,
	{
		{1},
		{2},
		{3}
	}
)

RETURN
CROSSJOIN(Table1, Table2)
Anonymous
Not applicable

Hi, I tried your code and I received the following error: "The syntax for 'Var' is incorrect. (DAX(GENERATE( GENERATESERIES(1,3), Var yr=YEAR(TODAY()) Var mo=MONTH(TODAY()) Var inc= mo + [Value] RETURN Row ("Date",Date(yr,inc,1))) Var Table2 = DATATABLE( "Value2", Integer, { {1}, {2}, {3} })RETURNCROSSJOIN(Table1, Table2)))."

 

I also tried removing the evaluate and naming the measure Start and I got the following error: 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

start = 
var Table1 = 
GENERATE(
    GENERATESERIES(1,3),
    Var yr=YEAR(TODAY())
    Var mo=MONTH(TODAY())
    Var inc= mo + [Value]
    RETURN 
    Row ("Date",Date(yr,inc,1))
)    

Var Table2 = 
DATATABLE(
	"Value2", Integer,
	{
		{1},
		{2},
		{3}
	}
)

RETURN
CROSSJOIN(Table1, Table2)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.