topic Re: Filtering table with DAX for at least one blank() in DAX Commands and Tips

Filtering table with DAX for at least one blank()

mhbon — Mon, 14 Dec 2020 10:33:21 GMT

Hi All,

Is there a way in which (using DAX or anything other besides M) I can filter out all the records where at least one column is blank?

For example if the source is:

Result should be:

to add on the matter; the key corresponds to a customer name in a table related to the types table. So the ideal result would be:

Reason I can't do it in M is because our BI team requires me to use a Power BI service dataset as source.

Any tips?

Thanks in advance!

Re: Filtering table with DAX for at least one blank()

amitchandak — Mon, 14 Dec 2020 13:03:43 GMT

mhbon , The measure which is showing X , should be changed to

calculate([measure], filter(Table, calculate(distinctcount(Table[Type]), all(Table)) =calculate(distinctcount(Table[Type]), allexcept(Table,Table[key]))))

Re: Filtering table with DAX for at least one blank()

nandukrishnavs — Mon, 14 Dec 2020 13:04:25 GMT

mhbon

If you are trying to represent the data in a table visual, you could write a DAX measure and apply a visual level filter.

Measure = VAR _type1 = SELECTEDVALUE ( 'Table'[Type1] ) VAR _type2 = SELECTEDVALUE ( 'Table'[Type2] ) VAR _type3 = SELECTEDVALUE ( 'Table'[Type3] ) VAR _type4 = SELECTEDVALUE ( 'Table'[Type4] ) VAR _type5 = SELECTEDVALUE ( 'Table'[Type5] ) VAR _result = IF ( ( ISBLANK ( _type1 ) || ISBLANK ( _type2 ) || ISBLANK ( _type3 ) || ISBLANK ( _type4 ) || ISBLANK ( _type5 ) ), "show", "hide" ) RETURN _result

Otherwise, you can request the data model owner to include a calculated column in the model. So, you can filter the data, page level, or report level.

CalculatedColumn = VAR _type1 = 'Table'[Type1] VAR _type2 = 'Table'[Type2] VAR _type3 = 'Table'[Type3] VAR _type4 = 'Table'[Type4] VAR _type5 = 'Table'[Type5] VAR _result = IF ( ( ISBLANK ( _type1 ) || ISBLANK ( _type2 ) || ISBLANK ( _type3 ) || ISBLANK ( _type4 ) || ISBLANK ( _type5 ) ), "show", "hide" ) RETURN _result

Regards,
Nandu Krishna

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 👍

Proud to be a Super User!

Re: Filtering table with DAX for at least one blank()

mhbon — Mon, 14 Dec 2020 13:23:49 GMT

That first one worked like a charm! Thanks!

Re: Filtering table with DAX for at least one blank()

mhbon — Mon, 14 Dec 2020 15:33:57 GMT

Hi nandukrishnavs or amitchandak

Additional diffictulty... 🙄 I requested the data in the model to be pivotted but this request was denied.

So now I'm wondering if there also a way to achieve this when the data looks like this:

and the preferred result would be this:

thanks again!

Re: Filtering table with DAX for at least one blank()

nandukrishnavs — Tue, 15 Dec 2020 05:21:51 GMT

mhbon

Measure = VAR _typeCount = 3 //change the type count VAR _x = CALCULATE ( COUNT ( 'Table'[Type] ), ALL ( 'Table'[Type] ) ) VAR _result = IF ( _x < _typeCount, "show", "hide" ) RETURN _result