How to filter for non-blank on distinct table

DAX DAX Calculations
1

I created a distinct table using Union and Distinct and want to exclude Blank category

As you see in the below chart, the Blank category shows up. I want to exclude this from my distinct table.

Thanks for your help

image

2

Try wrapping the Union equation into a =CALCULATETABLE() function.

Maybe something along the lines of =CALCULATETABLE(Union equation,[Marketing Assets] <> ā€œBlankā€).

Have the union as the table being calculated, and then the argument being made is that the marketing assets column cannot equal ā€œBlankā€

3

Thank you Zhekezhi but it gives me this error. It must be my Dax syntax.

image
image1669Ɨ206 18.4 KB

4

The filter getting rid of Blanks canā€™t be the first argument in a CALCULATETABLE function. The first argument has to be a table. if you put the filter in the second position do you get the same error?

5

I get this error now. It states each table argument of ā€˜Unionā€™ must have the same number of columns.

image

6

Let me upload a WIP. One second

7

WIP File.pbix (13.0 MB)

8

To get it working, itā€™s not ideal, but you can calculate another table by referencing the current one with:

Marketing Assets Calculate = CALCULATETABLE(ā€˜Marketing Asset TopNā€™,FILTER(ā€˜Marketing Asset TopNā€™,ā€˜Marketing Asset TopNā€™[Marketing Assets] <> ā€œBlankā€))
image

Let me play around to try and avoid needing two tables.

9

It must have been syntax, as I just got this to work in the original table:

Marketing Asset TopN =
CALCULATETABLE(UNION(
DISTINCT(ā€˜Marketing Assetsā€™[Marketing Assets]),
DATATABLE(ā€œMarketing Assetsā€,STRING,{{ā€œOthersā€}})),FILTER(ā€˜Marketing Assetsā€™,ā€˜Marketing Assetsā€™[Marketing Assets] <> ā€œBlankā€)
)

image
image889Ɨ95 4.9 KB

10

Thanks Zherkezhi. It works fine.

Much appreciated.

1 Like
11

Can you send over the WIP file?

Thanks