Except fuction Returning data which exists on both table.

[amaan91]

Hi All , 

I am generating two summarised virtual tables ( My_Table, My_Table_LY)  with a list of customers and their revenue and volume  to eventually generate a third table which does not have the common values form both table buy using an except function. 

if i am using a table with only one collumn (Name) in both tables i can get my expected result but as soon as i add two extra columns(Volume and revenue) in both tables i am getting a result doesnot make sence. 

i need to further filter based the calculated columns("Accepted Jobs2")  

Thanks for the help in advance. 

Syntax - 

Result = EXCEPT(   My_table_LY , My_table )

VAR My_table =

               SUMMARIZE (

        FILTER ( ALL ( Append1) ,

            Append1[StatusDateTime] >= Start_Date &&

            Append1[StatusDateTime] <= End_Date

        ),

        Append1[CustomerList_Filtered],

        "Accepted Jobs2", [Accepted Jobs.AG],

        "Accepted Revenue2", [Accepted Sales.AG]

    )

VAR My_table_LY =

               SUMMARIZE (

        FILTER (

            ALL ( Append1),

            Append1[StatusDateTime] >= start_Date_LY &&

            Append1[StatusDateTime] <= End_Date_LY

        ),

        Append1[CustomerList_Filtered],

        "Accepted Jobs2", [Accepted Jobs.AG],

        "Accepted Revenue2", [Accepted Sales.AG]

    )

[bhanu_gautam]

@amaan91 ,

Create Summarized Tables: Ensure that both My_table and My_table_LY have the same structure and data types.
Use the EXCEPT Function: Use the EXCEPT function to get the rows that are in My_table_LY but not in

VAR My_table =
SUMMARIZE (
FILTER (
ALL ( Append1 ),
Append1[StatusDateTime] >= Start_Date &&
Append1[StatusDateTime] <= End_Date
),
Append1[CustomerList_Filtered],
"Accepted Jobs2", [Accepted Jobs.AG],
"Accepted Revenue2", [Accepted Sales.AG]
)

VAR My_table_LY =
SUMMARIZE (
FILTER (
ALL ( Append1 ),
Append1[StatusDateTime] >= start_Date_LY &&
Append1[StatusDateTime] <= End_Date_LY
),
Append1[CustomerList_Filtered],
"Accepted Jobs2", [Accepted Jobs.AG],
"Accepted Revenue2", [Accepted Sales.AG]
)

VAR Result =
EXCEPT (
My_table_LY,
My_table
)

RETURN
Result

[amaan91]

That is exacly what i have done , but i still not getting the desired result.

[Rakesh1705]

I guess LY means Last year and Last year will have less number of rows.  Hence try with EXCEPT(My_table,My_table_LY). If it doesn't work, please provide a snap of the source data.

[amaan91]

I have already done that and this is wt its giving me 

MY_Table
list	jobs	rev
4 Corners Picture Framers	1	27.8
​​NSW Registry of Births Deaths & Marriages	1	180
"SWEET JANE"	1	270
John	1	250

MY_Table_LY
list	jobs	rev
4 Corners Picture Framers	1	34.046
​​NSW Registry of Births Deaths & Marriages	1	180
"SWEET JANE"	1	270
ben	2	500

Except Table
list	jobs	rev
4 Corners Picture Framers	1	27.82
John	1	250

But I want 

Expected Result
list	jobs	rev
John	1	250

[Anonymous · Accepted solution]

Hi @amaan91 ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a measure as below

Flag = 
VAR _list =
    SELECTEDVALUE ( 'MY_Table'[list] )
RETURN
    IF ( _list IN VALUES ( 'MY_Table_LY'[list] ), 0, 1 )

2. Create a table visual and apply the fields of table 'MY_Table' on it

3. Apply a visual-level filter on the above table visual with the condition (Flag is 1)

Best Regards