How to interpreted expression for PnL_Table ?

[admin11]

Hi All

I have 2 P&L report working fine.

Try to combine 2 P&L report into 1.

After combine both of them into 1 , I get error.

Now i need to understand what is below code doing :-

_PNL_Table_S =

VAR t_rev =

FILTER ( DISTINCT ( GL_S[1_REV] ), NOT ( ISBLANK ( GL_S[1_REV] ) ) )

VAR t_cogs =

FILTER ( DISTINCT ( GL_S[1_COGS] ), NOT ( ISBLANK ( GL_S[1_COGS] ) ) )

VAR t_oth =

FILTER ( DISTINCT ( GL_S[1_OTH] ), NOT ( ISBLANK ( GL_S[1_OTH] ) ) )

VAR t_exp =

FILTER ( DISTINCT ( GL_S[1_EXP] ), NOT ( ISBLANK ( GL_S[1_EXP] ) ) )

VAR t_rev_cogs =

UNION ( t_rev, t_cogs, t_oth, t_exp )

VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}

RETURN

tbl

Hope some one can share with me.

Enclosed my PBI file which no error , Because when i remove all the data , the error dis-appear.

https://www.dropbox.com/s/1nxoj96arnr5o5f/PBTnS%20GL%20Ver007%20NO%20ERROR.pbix?dl=0

Paul Yeo

[PaulDBrown]

It's difficult to see where the issue is. However, JFYI, You don't need to filter DISTINCT by no blanks; DISTINCT returns a table of unique values excluding blanks..

[admin11]

@PaulDBrown 

Thank you for sharing.

Below is my orginal syntax :-

_PNL_Table =
VAR t_rev =
FILTER ( DISTINCT ( GL[1_REV] ), NOT ( ISBLANK ( GL[1_REV] ) ) )
VAR t_cogs =
FILTER ( DISTINCT ( GL[1_COGS] ), NOT ( ISBLANK ( GL[1_COGS] ) ) )
VAR t_oth =
FILTER ( DISTINCT ( GL[1_OTH] ), NOT ( ISBLANK ( GL[1_OTH] ) ) )
VAR t_exp =
FILTER ( DISTINCT ( GL[1_EXP] ), NOT ( ISBLANK ( GL[1_EXP] ) ) )
VAR t_rev_cogs =
UNION ( t_rev, t_cogs, t_oth, t_exp )
VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}
RETURN
tbl

Below syntax is where i remove filter Blank :-
_PNL_Table =
VAR t_rev =
FILTER ( DISTINCT ( GL[1_REV] ))
VAR t_cogs =
FILTER ( DISTINCT ( GL[1_COGS] ))
VAR t_oth =
FILTER ( DISTINCT ( GL[1_OTH] ))
VAR t_exp =
FILTER ( DISTINCT ( GL[1_EXP] ))
VAR t_rev_cogs =
UNION ( t_rev, t_cogs, t_oth, t_exp )
VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}
RETURN
tbl

I get error msg :-

My PBI file :-

https://www.dropbox.com/s/jt7drogywitrrg5/PBS%20GL%20ASK%20HOW%20TO%20REMOVE%20BLANK_.pbix?dl=0

Paul

[PaulDBrown · Accepted solution]

@admin11 

Remove the FILTER expression. It's not needed

[admin11]

@PaulDBrown 

Thank you it work fine now.

Paul