DAX table creation depended by variables

[KaZeK]

Dear Community, 

I am Power BI beginner, but I'm trying to use already existing knowledge on this forum. Today, I created account because I need to ask you for support for my issue which I faced because I have no idea how to make it...

I have variables:

VAR SR30MIN = -100

VAR SR30MAX = 800

VAR PeriodQuantity = ((SR30MAX + ABS(SR30MIN))/100)*5

VAR PeriodRange = SR30MAX + ABS(SR30MIN)

VAR PeriodStep = PeriodRange / PeriodQuantity

VAR LoopTable = GENERATESERIES(SR30MIN, SR30MAX, PeriodStep)

VAR MainTable = ADDCOLUMNS(LoopTable, PeriodStep?, ?)

Return

Main Table

How to find parameters for ADDCOLUMN() function to get table like:

Value               LoopName          
SR30MIN         Period(1)
...                      Period(n+1)

...                      ...

...                      ...

SR30MAX        Period(PeriodQuantity)

Thanks in advance for any suggestions!

Regards

Kazek

[amitchandak]

@KaZeK , GENERATESERIES will give you a column Value , which you can use in Addcolumns to ahve some calculation

Example we are using date from the calendar to calculate other columns

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

[KaZeK]

Hello Amitchandak,

Based on your responce I see that this is not exactly what I need. I would like to create new table based on the GENERATESERIES() function...

I tried to do something like below, but it doesnt work, probably there are some mistakes, can you have a look please?

VAR RangeSeries = GENERATESERIES(MINSR30, MAXSR30, PeriodStep),

VAR PeriodNo = GENERATESERIES(1, PeriodQuantity, 1)

MyTable = GENERATE(

      SR30RangeLOW = [RangeSeries],

      SR30RangeHI = [RangeSeries] + PeriodStep,

      SR30Name = "Period" && PeriodNo

)

I know that function Generate() can have only 2 arguments, but how can I create table with 3 columns: 

SR30RangeLOW, SR30RangeHI and SR30Name which will take values from GENERATESERIES() function.

Both GENERATESERIES() functions generated the same amount of raws.

Regards
Kazek