Forum Discussion
Filters not applied in my table
Hi Guys,
Below is my Model Diagram, I just need to create a calculated table for Employee id = 3.
Please only use "Employee Id" column from "EMPLOYEE HIEARACHY" Table.
Attached the file in google drive ( Download from below link ) ::
https://drive.google.com/file/d/1KVrAl8p4skS6iM_VDoPLV-OuxvKEwC8u/view?usp=sharing
Correct Result : table of "ProductName" column with 74 rows foe employeeid : 3
DAX used for Calculated Table :
Employee no: 3 (ordered Product ID) =
Var table1 =
FILTER(
VALUES(Products[ProductName]),
Products[ProductName] =
CALCULATE(MAX('Order Details'[Product Name]),
'Employee Hierarchy'[Employee ID] = 3,
TREATAS(values('Employee Hierarchy'[Employee ID]),Employees[EmployeeID])
)
)
return
table1
Note : Please don't use Employee ID from "EMPLOYEE" Table
Employee Not Procured Item count = Var table1 = CALCULATETABLE(VALUES('Order Details'[ProductID]), TREATAS(VALUES('Employee Hierarchy'[Employee ID]),Employees[EmployeeID]) ) return countrows(except(VALUES(Products[ProductID]),table1))
4 Replies
- lbendlin
Super User
CALCULATE(MAX('Order Details'[Product Name]),So you want to report on the sales for the product that has the highest alphabetical name? Please clarify.
Since you want to create a static calculated table you don't need to use TREATAS .
I need to find the list of Product Items not procured by employee.
So, for example if you consider EmployeeID : 3, he did not procure 3 products out of 77 products.
Hence create a calculated table (static one for employee no: 3) and let me know what products he didn't procure so far.
This is the code I used to obtain the output
Table 4 =ADDCOLUMNS(SUMMARIZECOLUMNS('Employee Hierarchy'[Full Name]),"Products Not Procured",Var table1 = CALCULATETABLE(FILTER(values('Products'[Productid]),Products[Productid] = CALCULATE(MAX('Order Details'[Productid]),TREATAS(VALUES('Employee Hierarchy'[Employee ID]),Employees[EmployeeID]))))var table2 = VALUES(Products[Productid])var except_table = EXCEPT(table2,table1)returnCONCATENATEX(except_table,[Productid],",",[Productid],ASC)--TREATAS(VALUES('Employee Hierarchy'[Employee ID]),Employees[EmployeeID])--CROSSFILTER('Order Details'[ProductID],Products[ProductID],Both))Let me know, how this is ?- lbendlin
Employee Not Procured Item count = Var table1 = CALCULATETABLE(VALUES('Order Details'[ProductID]), TREATAS(VALUES('Employee Hierarchy'[Employee ID]),Employees[EmployeeID]) ) return countrows(except(VALUES(Products[ProductID]),table1))
I appreciate your code work 🙂
