topic Re: Except fuction Returning data which exists on both table. in DAX Commands and Tips

Except fuction Returning data which exists on both table.

Anonymous — Fri, 05 Jul 2024 09:07:46 GMT

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]

)

Re: Except fuction Returning data which exists on both table.

Anonymous — Fri, 05 Jul 2024 09:26:49 GMT

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.

Re: Except fuction Returning data which exists on both table.

bhanu_gautam — Fri, 05 Jul 2024 09:47:19 GMT

Anonymous ,

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

Re: Except fuction Returning data which exists on both table.

Anonymous — Tue, 09 Jul 2024 05:38:42 GMT

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

Re: Except fuction Returning data which exists on both table.

Anonymous — Wed, 10 Jul 2024 04:19:24 GMT

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

Re: Except fuction Returning data which exists on both table.

Anonymous — Fri, 19 Jul 2024 08:06:59 GMT

Hi Anonymous ,

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