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.