Forum Discussion

admin11's avatar
admin11
Memorable Member
5 years ago
Solved

How to interpreted expression for PnL_Table ?

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.
 
Paul Yeo
 

 

 

Need Help

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
      admin11
      Memorable Member
      5 years ago

      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