Forum Discussion
Create a dynamic filter for budget selection
ParthSoni1901 OK, so you could approach this like the following. If you post the table of data as text I could test the code out. Otherwise, I'm just guessing something like this should work. Note that I have no idea how you want to return this in terms of the visual, I just used a card visual. Note that in my model, I was using Table4 as my base data table.
First measure (Measure 2) guarantees the sort order such that the highest ROI is listed first.
Measure 2 =
VAR __Budget = 20000
VAR __Count = COUNTROWS('Table4') //Get count of rows in context
VAR __Ordered = CONCATENATEX('Table4',[Campaigns] & "*" & [Analyzed ROI] & "*" & [Cost],"|",[Analyzed ROI],DESC)
//this returns a string like SEM*8.15*10000|Instagram*8.1*10000 that is ordered by ROI
VAR __Table1 =
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Row",PATHITEM(__Ordered,[Value],TEXT)
)
VAR __Table2 =
ADDCOLUMNS(
__Table1,
"__Campaigns",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,1,TEXT),
"__Analyzed ROI",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,2,TEXT) + 0,
"__Cost",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,3,TEXT) + 0
)
VAR __Table3 =
ADDCOLUMNS(
__Table2,
"__RT",
VAR __ROI = [__Analyzed ROI]
RETURN
__Budget - SUMX(FILTER(__Table2,[__Analyzed ROI] >= __ROI),[__Cost])
)
VAR __FinalTable = FILTER(__Table3, [__RT] >= 0)
RETURN
CONCATENATEX(__FinalTable,[__Campaigns] & "|" & [__Analyzed ROI] & "|" & [__Cost] & "|" & [__RT],UNICHAR(10))
This version does not guarantee sort order but is simpler:
Measure 3 =
VAR __Budget = 20000
VAR __Table =
ADDCOLUMNS(
'Table4',
"__RT",
VAR __ROI = [Analyzed ROI]
RETURN
__Budget - SUMX(FILTER('Table4',[Analyzed ROI] >= __ROI),[Cost])
)
VAR __FinalTable = FILTER(__Table, [__RT] >= 0)
RETURN
CONCATENATEX(__FinalTable,[Campaigns] & "|" & [Analyzed ROI] & "|" & [Cost] & "|" & [__RT],UNICHAR(10))
The first measure converts the table to as string ordered by ROI. This is then converted back to a table by using a FOR loop (__Table1) and then parsing out the string text (__Table2). This guarantees sort order. Then, a WHILE loop (__Table3) to figure out when you have used up your budget. The (__FinalTable) "exits" the WHILE loop.
Measure 3 just uses the WHILE loop.