Forum Discussion
Direct Query - Measure that applies filters like a slicer
Is there a way to create a measure that applies filters in direct query in the same way as a slicer? I have found that the SQL generated from a CALCULATE statement is different from the SQL generated by performing the same task using a slicer.
For illustration purposes, let's imagine there are 3 tables in direct query mode (Ledger, CompanyTable, and AccountTable). If I sum the "Currency" column in Ledger, and apply filters using a slicer to both other tables, it generates a query like this:
Scenario 1 - Slicers Only
Select sum(T1.Currency) FROM (SELECT Currency ,FK_Companies , FK_Accounts from Ledger) as [T0] Left Outer Join (SELECT CompanyName ,PK_Companies from CompanyTable) as [T1] On T0.FK_Companies = T1.PK_Companies Left Outer Join (SELECT Accounts ,PK_Accounts from AccountTable) as [T2] On T0.FK_Accounts = T2.PK_Accounts WHERE T1.CompanyName in (N'Company1',N'Company2') and T2.Accounts in (N'100',N'101',N'102')
If I leave the slicer on the company table and instead try try to apply the same filter for Accounts using a calculate statement:
=Calculate(sum(Ledger[Currency]),filter(AccountTable,AccountTable[Accounts]="100" || AccountTable[Accounts]="101" || AccountTable[Accounts]="102"))
It will generate a query like this:
Scenario 2 - Slicers + Filter
Select sum(T3.Currency) From ( Select FK_Accounts, sum(T1.Currency) FROM (SELECT Currency ,FK_Companies , FK_Accounts from Ledger) as [T0] Left Outer Join (SELECT CompanyName ,PK_Companies from CompanyTable) as [T1] On T0.FK_Companies = T1.PK_Companies WHERE T1.CompanyName in (N'Company1',N'Company2') Group By FK_Accounts ) [T3] Inner Join ((SELECT Accounts ,PK_Accounts from AccountTable) as [T2] Where T2.Accounts in (N'100',N'101',N'102') ) [T4] On T3.FK_Accounts = T4.PK_Accounts
Scenario 2 is obviously less efficient. How can I write a CALCULATE statement so that I get the first result in direct query.
Thanks!
Okay, I figured it out. You have to apply the filter statement like this:
filter(Ledger, related(AccountTable[Accounts])="100" || related(AccountTable[Accounts])="101" || related(AccountTable[Accounts])="102" )
Seems clunky... if anybody knows a more efficient way, I'd be interested to hear it!
1 Reply
- tloF2D5U9E5Regular Visitor
Okay, I figured it out. You have to apply the filter statement like this:
filter(Ledger, related(AccountTable[Accounts])="100" || related(AccountTable[Accounts])="101" || related(AccountTable[Accounts])="102" )
Seems clunky... if anybody knows a more efficient way, I'd be interested to hear it!