Forum Discussion
Issue to translate sql logic to dax
Hi everyone,
I’m working on a Power BI model where I need to replicate a two-step SQL pattern involving two fact tables and would like guidance on the best DAX approach.
Model overview
A dimension table (e.g. Employee / Entity master)
1 → * FactTable_A
1 → * FactTable_B
FactTable_A and FactTable_B are not directly related (by design).
SQL pattern being replicated
Step 1 – Derive a list of entities from FactTable_A
SELECT EntityID FROM FactTable_A WHERE <multiple business filters>In Power BI, this logic is implemented using slicers and visuals on FactTable_A.
Step 2 – Filter FactTable_B using the entity list from Step 1
SELECT EntityID, Attribute, Date FROM ( SELECT EntityID, Attribute, Date, ROW_NUMBER() OVER ( PARTITION BY EntityID, Attribute ORDER BY Date DESC ) AS rn FROM FactTable_B WHERE <static filters> AND EntityID IN ( /* result of Step 1 */ ) ) t WHERE rn = 1;
However, I am unable to replicate this. can you please help with dax?
Hi k_h_s
et filtered EntityIDs from FactTable_A
FilteredEntities =
CALCULATETABLE(
VALUES(FactTable_A[EntityID]),
-- Add your business filters here
FactTable_A[SomeColumn] = "SomeValue"
)
Filter FactTable_B using these EntityIDsFilteredFactB =
FILTER(
FactTable_B,
FactTable_B[EntityID] IN FilteredEntities
&& FactTable_B[OtherStaticFilter] = "SomeValue"
)
Get latest row per EntityID + AttributeLatestFactB =
FILTER(
FilteredFactB,
RANKX(
FILTER(FilteredFactB,
FactTable_B[EntityID] = EARLIER(FactTable_B[EntityID]) &&
FactTable_B[Attribute] = EARLIER(FactTable_B[Attribute])
),
FactTable_B[Date],
,
DESC,
DENSE
) = 1
)Try these solutions and let me know if you need further clarification
Regards,
Rufyda Rahma | MIEHi k_h_s - as per above mentioned, information do not remove filter context from FactTable_A.
you can try the below measue:
Latest FactB Date =
VAR EntityList =
VALUES ( FactTable_A[EntityID] )
RETURN
CALCULATE (
MAX ( FactTable_B[Date] ),
-- Static filters on FactTable_B
FactTable_B[OtherStaticFilter] = "SomeValue",
-- Apply Entity list from FactTable_A (SQL IN equivalent)
TREATAS (
EntityList,
FactTable_B[EntityID]
)
)Hope this helps.
4 Replies
- Rufyda
Super User
Hi k_h_s
et filtered EntityIDs from FactTable_A
FilteredEntities =
CALCULATETABLE(
VALUES(FactTable_A[EntityID]),
-- Add your business filters here
FactTable_A[SomeColumn] = "SomeValue"
)
Filter FactTable_B using these EntityIDsFilteredFactB =
FILTER(
FactTable_B,
FactTable_B[EntityID] IN FilteredEntities
&& FactTable_B[OtherStaticFilter] = "SomeValue"
)
Get latest row per EntityID + AttributeLatestFactB =
FILTER(
FilteredFactB,
RANKX(
FILTER(FilteredFactB,
FactTable_B[EntityID] = EARLIER(FactTable_B[EntityID]) &&
FactTable_B[Attribute] = EARLIER(FactTable_B[Attribute])
),
FactTable_B[Date],
,
DESC,
DENSE
) = 1
)Try these solutions and let me know if you need further clarification
Regards,
Rufyda Rahma | MIE - rajendraongole1
Hi k_h_s - as per above mentioned, information do not remove filter context from FactTable_A.
you can try the below measue:
Latest FactB Date =
VAR EntityList =
VALUES ( FactTable_A[EntityID] )
RETURN
CALCULATE (
MAX ( FactTable_B[Date] ),
-- Static filters on FactTable_B
FactTable_B[OtherStaticFilter] = "SomeValue",
-- Apply Entity list from FactTable_A (SQL IN equivalent)
TREATAS (
EntityList,
FactTable_B[EntityID]
)
)Hope this helps.
Hi Rufyda , I tried to apply this solution in a model consisting all 3 tables and it worked, thank you. However, in my case, the tables are in 3 different semantic models and in a new report, I connect these 3 tables (a->b, a->c). So, when I try to use same dax it gives me an error saying "The resultset of a query to external data source has exceeded the maximum allowed size od '1000000' rows". Hence, I am still stuck. Can you please help with that?
Thank you- cengizhanarslan
You can replicate that SQL pattern with a virtual relationship in DAX.
The key is: take the Entity list coming from FactTable_A filter context, then apply it to FactTable_B with TREATAS(), and finally do the “latest row per EntityID+Attribute” using TOPN / MAXX patterns.
Example measure that returns the latest Date per Entity+Attribute from FactTable_B, filtered by the entities selected via FactTable_A:
Latest Date (B, filtered by A) = VAR EntityList = VALUES ( 'DimEntity'[EntityID] ) -- entities currently in context via FactTable_A slicers RETURN CALCULATE ( MAX ( FactTable_B[Date] ), -- latest date in B TREATAS ( EntityList, FactTable_B[EntityID] ), FactTable_B[StaticFlag] = 1 -- your static filters on B )If you need the latest row value (e.g., Attribute value at the latest date), use TOPN:
Latest Value (B) = VAR EntityList = VALUES ( 'DimEntity'[EntityID] ) VAR RowsB = CALCULATETABLE ( FactTable_B, TREATAS ( EntityList, FactTable_B[EntityID] ), FactTable_B[StaticFlag] = 1 ) VAR LastRow = TOPN ( 1, RowsB, FactTable_B[Date], DESC ) RETURN MAXX ( LastRow, FactTable_B[SomeValue] )Put DimEntity[EntityID] and FactTable_B[Attribute] on rows of a matrix and use the measure — it behaves like your SQL ROW_NUMBER()...WHERE rn=1, but respects the entity list coming from FactTable_A selections.
