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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors