Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 -
Solved! Go to 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
@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
Proud to be a Super User! |
|
That is exacly what i have done , but i still not getting the desired result.
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 | ||
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 |
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |