Forum Discussion

SoufTC's avatar
SoufTC
Helper 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

     




    Showcase Report – Contoso By SpartaBI


          

SoufTC's avatar
SoufTC
Helper 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
Community 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

 




Showcase Report – Contoso By SpartaBI