cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dv9009 Occasional Visitor
Occasional Visitor

Composite Model and Direct query, loop up values from 2 tables

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 259 members 2,917 guests
Please welcome our newest community members: