Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
amaan91
Helper I
Helper I

Except fuction Returning data which exists on both table.

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 (
        FILTERALL ( 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]
    )
 





1 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)

vyiruanmsft_0-1721376320405.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
bhanu_gautam
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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

Rakesh1705
Super User
Super User

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.

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

MY_Table  
listjobsrev
4 Corners Picture Framers127.8
​​NSW Registry of Births Deaths & Marriages1180
"SWEET JANE"1270
John1250

 

MY_Table_LY  
listjobsrev
4 Corners Picture Framers134.046
​​NSW Registry of Births Deaths & Marriages1180
"SWEET JANE"1270
ben2500

 

Except Table   
listjobsrev
4 Corners Picture Framers127.82
John1250


But I want 

Expected Result   
listjobsrev
John1250



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)

vyiruanmsft_0-1721376320405.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.