Forum Discussion

SoufTC's avatar
SoufTC
Icon for Helper I rankHelper I
4 years ago
Solved

Delete rows based on calculated number

I’m having trouble to delete rows based on calcluted # number.   I have 3 tables.   1. A table that defines unique list of call (call)[List] per day (call)[Date]. each call has an answer status (...
DAX
delete rows
Help me
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    SoufTC this was super super tricky.
    Also, you had mistakes in your tables you copied (look at the first table you pasted), so make sure you test this with accurate data.
    Attaching the file:
    Delete Rows.pbix
    It involved extra neccesary steps in Power Query so make sure you follow them and then I could have written this calculated table:

     

    Calc Table = 
VAR _tbl1 = 
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                FactCalls,
                "@Removal Index", 
                IF(
                    RELATED(DimStatus[Names]) <> "drop out", 
                    0,
                    RANKX(FILTER(FactCalls,FactCalls[Key] = EARLIER(FactCalls[Key])), FactCalls[Index], ,ASC) - COUNTROWS(FILTER(FactCalls, FactCalls[Status] <> "DO" && FactCalls[Key] = EARLIER(FactCalls[Key])))
                ) - RELATED(DimDrops[# drop])
            ),
            [@Removal Index] < 0
        ),
        "List", [List],
        "Status", [Status],
        "Date", [Date],
        "Key", [Key]
    )
RETURN
    _tbl1

     




    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

SoufTC's avatar
SoufTC
Icon for Helper I rankHelper I
4 years ago

SpartaBIthank's for your response. from the table Number we define how many rows to delete according to the (Number)[#drop]: for example according to Table Number for the (Call)[Key] = List1 2022-02-09  we have (Number)[#drop] = 2 rows to delete. In (call)[List] we have 3 rows = DO so we keep delete 2 and keep 1. I don't know if clear enough thank's in advance

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

SoufTC this was super super tricky.
Also, you had mistakes in your tables you copied (look at the first table you pasted), so make sure you test this with accurate data.
Attaching the file:
Delete Rows.pbix
It involved extra neccesary steps in Power Query so make sure you follow them and then I could have written this calculated table:

 

Calc Table = 
VAR _tbl1 = 
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                FactCalls,
                "@Removal Index", 
                IF(
                    RELATED(DimStatus[Names]) <> "drop out", 
                    0,
                    RANKX(FILTER(FactCalls,FactCalls[Key] = EARLIER(FactCalls[Key])), FactCalls[Index], ,ASC) - COUNTROWS(FILTER(FactCalls, FactCalls[Status] <> "DO" && FactCalls[Key] = EARLIER(FactCalls[Key])))
                ) - RELATED(DimDrops[# drop])
            ),
            [@Removal Index] < 0
        ),
        "List", [List],
        "Status", [Status],
        "Date", [Date],
        "Key", [Key]
    )
RETURN
    _tbl1

 




In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

Showcase Report – Contoso By SpartaBI


Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

SpartaBI Logo