topic Re: Delete empty rows using Power BI DAX in DAX Commands and Tips

Delete empty rows using Power BI DAX

PreetiSahu321 — Sun, 16 Feb 2020 09:52:09 GMT

Hello Power BI Community Members,

Below represents a dummy Power BI table with one ID Column, Four date columns and one project name column which makes a couple of duplicates.

Here one project name had the date information which I need and one did not have any date data that means I want to delete the duplicate rows where there is no data under the 'dates' columns.

I want to delete these empty rows (i.e. Project 1 with ID 111, Project 3 and Project 4) by using the Power BI DAX.

Can anyone suggest to me what I have to do in this case?

Thanks in Advance!!!

Re: Delete empty rows using Power BI DAX

az38 — Sun, 16 Feb 2020 14:58:49 GMT

Hi PreetiSahu321

there are 2 options

first (and prefferably from my point of view), you can just set as inactive "Show items with no data" in visual field Project name settings

second, you can create a calculated table like

Filtered Table = FILTER( ALL('Table'), NOT(ISBLANK('Table'[Date1])) || NOT(ISBLANK('Table'[Date2])) || NOT(ISBLANK('Table'[Date3])) || NOT(ISBLANK('Table'[Date4])) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Delete empty rows using Power BI DAX

AlexAlberga727 — Sun, 16 Feb 2020 15:32:14 GMT

Interesting approach above. I attempted the above solution and resulted in an error.

However, I was able to replicate a solution using measures to calculate the MAX Date for each date column.

MAX Date1 = MAX ( table[date1] )

MAX Date2 = MAX ( table[date2] )

MAX Date3 = MAX ( table[date3] )

MAX Date4 = MAX ( table[date4] )

Visual is Table w/

Project, and the newly created measures.

Re: Delete empty rows using Power BI DAX

az38 — Sun, 16 Feb 2020 15:53:11 GMT

PreetiSahu321

also, you can try a calculated column

isThereAnyDate = if(ISBLANK('Table'[Date1]) && ISBLANK('Table'[Date2]) && ISBLANK('Table'[Date3]) && ISBLANK('Table'[Date4]),false(),true())

then filter your data by isThereAnyDate = true condition

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Delete empty rows using Power BI DAX

az38 — Sun, 16 Feb 2020 15:54:32 GMT

AlexAlberga727

i see the only possible error in the statement above - using comma "," as delimiter but not semicolon ":". It is the locale question and it is not error

do not hesitate to give a kudo to useful posts and mark solutions as solution