Hi all,
I have a Dax formula that retrieves distinct values from a table using Union and AllNoBlankRows.
TOPN Account Type =
Union(
ALLNOBLANKROW(
Prospects[Account Type]),
{“Others”})
but for some reason the table still shows blank values. What am I doing wrong? Thank you in advance.
Please see image below.
Found the solution through this forum and answer by Alberto Ferrari of SQBLI.
Solved: Re: Use ALLNOBLANKROW with UNION - Microsoft Power BI Community
Basically, this Dax function serves no useful purpose for to remove blank rows. All you have to do is add filter to remove non blank rows. It worked perfectly.
Hello @ysherriff,
Thank You for posting your query onto the Forum.
The reason why “ALLNOBLANKROW()” is still yielding the result as a blank row is because you must be having a blank row as a value by default in your table. It ignores the blank row when it’s created due to weak relationship but not due to the actual presence of it inside the table itself.
Since blank row is present by default as a value inside your table for that particular column. You can remove it by specifying it inside the “FILTER()” function. Below is the link of a video provided for the reference from SQLBI where Alberto shows how to remove the blank rows in those type of scenario’s. You can refer the video from “5:00 minute” mark.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
Thank you Harshike always.
