Question regarding Composite models
I have a Direct Query Table containg tons of data - DqTable(codes, date, name, status)
I have an imported table containing all the rules - RulesTable(name,status, newstatus)
I want to count number of codes which satisfy all the rules
Ex.
DqTable:
codes date name status
45852 1/2/19 ABC Completed
78513 1/3/19 DCV WIP-1
65753 1/3/19 ABC Not Completed
RulesTable:
name status newstatus
ABC Completed C1
ABC WIP C2
ABC Not Completed C3
DCV WIP-1 C2
[name] is common and I can make a relationship.
I tried to make a calculated table and failed.
I tried lookup function it works good as a measure but I used SELECTEDVALUE TO Select each code - but i am not able to save the results.
So i want to do it in calculated table, i want the below result
date name C1 C2 C3
1/2/19 ABC 1 - -
I tried this code:
CalcTable: ADDCOLOUMNS(SUMMURIZE(DqTable, DqTable[date], DqTable[names]), "C1", Calculate(COUNT(DqTable[codes]), LOOKUPVALUE(RulesTable[newstatus],RulesTable[status],DqTable[status], RulesTable[name],DqTable[name]) = "C1")
But i got LOOKUPVALUE function cannot be used as a Table Filter, I tried passing LOOKUPVALUE as a var, but it is not detecting DqTable[status]
Its complex please try to help me and let me know if you want any details
