Filtering table with DAX for at least one blank()

[mhbon]

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!

M

[mhbon]

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! 

[nandukrishnavs]

@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

[nandukrishnavs · Accepted solution]

@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

[mhbon]

That first one worked like a charm! Thanks! 

[amitchandak]

@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]))))