Filter blank values in dax table

[alexvor]

Hello all.

I use a "bridge table" by joining some distinct values from two tables. But recently the update stoped working because of some blank values.

How can i filter these blank values in the following dax formula? Thanks in advance.

Bridge Table =

ADDCOLUMNS (

CROSSJOIN (

CROSSJOIN (

DISTINCT ( UNION ( VALUES ( 'Base Despesas'[Centro de Custo] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Centro de Custo] ) ) );

DISTINCT (

UNION ( VALUES ( 'Base Despesas'[Conta contábil D365] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Conta Contábil] ) )

)

);

DISTINCT ( UNION ( VALUES ( 'Base Despesas'[Mês] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Mês] ) ) )

);

"UniqueKey"; [Centro de Custo] & "-"

& [Conta contábil D365]

& "-"

& [Mês]

)

[az38 · Accepted solution]

Hi @alexvor 

maybe 

Bridge Table =
ADDCOLUMNS (

FILTER(
CROSSJOIN (
CROSSJOIN (
DISTINCT ( UNION ( VALUES ( 'Base Despesas'[Centro de Custo] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Centro de Custo] ) ) );
DISTINCT (
UNION ( VALUES ( 'Base Despesas'[Conta contábil D365] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Conta Contábil] ) )
)
);
DISTINCT ( UNION ( VALUES ( 'Base Despesas'[Mês] ); VALUES ( GeneralJournalAccountEntrySNFEntity[Mês] ) ) )
);
NOT(ISBLANK([Centro de Custo]))
)

;
"UniqueKey"; [Centro de Custo] & "-"
& [Conta contábil D365]
& "-"
& [Mês]
)

[alexvor]

Thanks! This worked fine.

[amitchandak]

You can have a filter on top of  crossjoin and filter

like filter(crossjoin(A,B),isnot blank(A[a]))