Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors