Forum Discussion
Delete empty rows using Power BI DAX
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!!!
4 Replies
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
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.
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
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
