SQL Store procedure transformation in Power BI

[Anonymous]

Is there any way to crate dynamic temp table base on user selection on slicers. (sp logic is that, selected filter data again used for filtering fianl table to get disere output). e.g. SQL sp parameter are (@report_value, @report_date). want to transform sp logic in power bi, user will select slicers & bussiness logic need to be work. sample query as follow. 

insert into temp ( code,type,des)

select a.code, a.type, b.des

from AA a join BB b on a.code=b.code

and b.country= @report_value                  (user - country slicer selected value)

and b. fdate <= @report_date                    (user - date slicer selected value)

and b.tdate> @report_date

          where a.type='TT'

and a.startdate <=@report_date

and a.extintdate >@report_date

I have try lot but can't success yet. Reprots should generate on user slicer section value that should be dynamic.

Greatly appreciate comments if it is possible using slicers (without parameters)

[Anonymous]

First join your AA and BB table in modeling table as per your data one to many or many to one.

Then create one date table which is not attached to any table in model.

Date=calendar("01/01/2000","12/31/2030")

Note : change dates as per your requirement.

Create after slicer for user input on date[Calendar date]

Then create one measure.

Filter=Sumx(Table_1(use fact table recommended),if(table1[county]=selectedvalue(Table1[county])&& table1[Type]="TT"

Table1[startdate] <=

Min(Date[Calendar_date]) && 

Table1[Exitdate] >

Min(Date[Calendar_date])

&& Related(Table2[fdate]) <=

Min(Date[Calendar_date]) && 

Related(Table2[tdate]) >

Min(Date[Calendar_date])

),1,0)

Add this measure into your visual level filter ans set measure is not 0.

If it resolves your problem give kudoes and mark it as solution.

[Anonymous]

Thanks for your reply....Its seems make some sence.

My requirement quite wired, I have to be dynamic table with couple of filtered column values.

Final result is acutally the output of 5 underline store procedures base on condition. These store procedure filtered by this temp table on every selection of slicers (date, country etc) they are around 6-7 paramter. Good enough alteast works with 2 slicer value. Dynamic temp table is fist step.

Thanks.

[Anonymous]

this will work for your shared sp logic. IF your SP have any other logic. Just share logic with me so that i can help you out.

Thanks,

Pravin Wattamwar

[Anonymous]

Thanks Pravin for your comment.

I will try to explain sub set of requirement logic.

Sp1: input parameters -@reportby, @reportvalue, @reportdate

Logic:                  If @reprotby='Region' insert into temp (code)

                          select a.code from AA a join CC c on a.code=c.code

                                   Join DD d on d.code=c.code

                                  where d.list=@reportvalue

                                   and c.edate>= (select max(x.edate) from CC x where x.code=c.code and x.edate>= @reportdate)

                                 AND B.Flag='T' and a.startdate <=@reportdate

                                and a.extintdate >@reportdate

                 ELSE IF  @reprotby='Country'

                              insert into temp (code) select a.code from from AA a join BB b on a.code=b.code

                               and b.country= @reportvalue

                              and b. fdate <= @reportdate

                              and b.tdate> @reportdate

                             where a.type='TT'   and       a.startdate <=@reportdate

                             and a.extintdate >@reportdate

              Else if

                               Same kind of couple of paramater logic;

              --above result set used to filter other table

                      update table type=a.type select a.type

                             from Codelist a join temp t on a.code=t.code

                                          where a.flag='Con'

                                        and a.sdate>= (select max(x.sdate) from Codelist x where x.code=a.code and x.sdate>= @reportdate)

                      update table Desc=a.Desc 

                            select a.Desc from Codelist a join temp t on a.code=t.code

                                where a.flag='Desc' and a.sdate>= (select max(x.sdate)

                                                  from Codelist x where x.code=a.code and x.sdate>= @reportdate)

  Out put sp1 - Temp table with 3 column (Code, Type, Desc)

------------------------------------------------------------------------------------

SP2 - @reportdate @source (report date same as above)

Logic :                  insert into Final table (code, type, desc, name)

                                 select t.code, t.type, t.desc, a.name

                                   from FF a join temp t on a.code=t.code

                                  and a. fdate <= @reportdate and a.tdate> @reportdate

                                   where a.type='RT'

                  update Fianl table (code, type, desc, Tax1) select t.code, t.type, t.desc, a.Tax1, from FF a join temp t on a.code=t.code                           and a. fdate <= @reportdate and a.tdate> @reportdate

                     where a.type='T1'

                  update Fianl table (code, type, desc, Tax2) select t.code, t.type, t.desc, a.Tax2,

                  from FF a join temp t on a.code=t.code

                  and a. fdate <= @reportdate and a.tdate> @reportdate

                 where a.type='T2'

Final out of SP2 6 columns which is used as it is in report.

---------------------------------------------------------------------------

There are around 4 SP which is more less same kind of logic & all are futher filtered on temp table output. Because of your interest on my post...made be rewrite in details.

[Anonymous]

why don't you run your sp in power BI with parameters. May be it will work for you.

[Anonymous]

Primary requirement was user should pass the parameters from service & then get the desire output. which is currently not possible (it is possible but dynamic values for user selection is not possible & 8 refresh limitations). Even, they are ready to use desktop (user < 25), but not happy with query parameter window look & easyness. (limitations - no cascading parameter, no date picker, no dynamic list query).