Delete empty rows using Power BI DAX

[PreetiSahu321]

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!!!

[az38]

@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

LinkedIn

[az38]

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

LinkedIn

[AlexAlberga727]

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.

[az38]

@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