drillthrough

3 Topics

Nested dynamic queries, easy in SQL - difficult in PowerBI/DAX
Assume a single large source table, which must be queried in two steps: Tab1: Key_A Key_B Key_C Val a1 b1 c2 1 a1 b2 c1 5 a2 b3 c1 3 a2 b4 c2 8 a2 b4 c1 1 a2 b1 c2 4 With a variable drillvar = "a2" and two queries you would get: Qry1: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c1' AND Key_A = drillvar GROUP BY Key_B Qry2: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c2' AND Key_A = drillvar GROUP BY Key_B Key_B SumVal b3 3 b4 1 Key_B SumVal b1 4 b4 8 In the next step, both queries would be joined (the "Nz" function is used here to handle null/blank values): Qry3: SELECT Qry1.Key_B, Nz(SumVal1) - Nz(SumVal2) AS Diff FROM Qry1 LEFT JOIN Qry2 ON Qry1.Key_B=Qry2.Key_B Result: Key_B Diff b3 3 b4 -7 The task seems trivial. I think in PowerBI with DAX plus a Drillthrough value instead of SQL plus a variable it should be easy to do. Unfortunately, I've been racking my brains for two days on how to solve this efficiently, i.e. especially with a large source table. Does anyone here have an idea?
Hartmut
3 years ago Place DAX Commands and Tips
999Views
0likes
4Comments
Removing drillthrough filters in a measure
I've been beating my head on the bricks on this one... I have one tab with a table of contracts to be renewed. You pick one and drillthrough to a details tab. On the details tab, it shows details for that one contract, BUT I also need to show some metrics for the total Account and the total Account Group. (Within the total company, there are many Account Groups. Each Account Group has multiple Accounts, and each Account can have multiple contracts, and I need to see performance metrics at the individual Contract, Account, and Account Group on the same page.) Since it's a drillthrough, the Contract filter is pulled in automatically. So I need to have measures that can remove the contract filter, but still keep the Account filter (based on the Account from the contract), and measures that keep the Account Group (but not the account or contract filter). I've been trying things like CALCULATE( SUM( 'ContractPayments'[Paid]), ALL('Accounts'[Account Group]) ) But this still gives me only the single contract, not the whole Account Group. So I tried to force it to only have a filter on the Account Group with: CALCULATE( SUM ( 'ContractPayments'[Paid]), FILTER('Accounts', 'Accounts'[Account Group] = SELECTEDVALUE('Accounts'[Account Group])) ) But it still only showed me the single contract. So it's keeping the filter context of the page from the drillthrough, but not allowing me to filter to just the Group based on the selection. So then I tried to hard code the Account Group just to see if that would work instead of using the SELECTEDVALUE function. No luck. Same exact result - shows the single contract. So then I tried this: CALCULATE( SUM( 'ContractPayments'[Paid]), ALLEXCEPT('Accounts','Accounts'[Account Group]) ) And that gives me the entire company (i.e. it removes *all* the filters, not just the Account Group Name). There's something fundamental I'm missing, obviously. I've tried adding extra filters both above and below the ALLEXCEPT statement. I've tried combinations of FILTER and REMOVEFILTERS and ALL and ALLEXCEPT, but I either get a result that shows only the contract (meaning it is keeping the page drillthrough filter and ignoring everything else I'm trying to do) or it's showing the whole company (meaning once I remove any part of the filter context, I can't have any other filter in effect). Can anyone shed some light on what I'm overlooking? I thought the whole point of CALCULATE was to be able to specify the *exact* filter you want for that measure, regardless of any other filter context - filters on the page, drillthrough, visual, etc... But no matter what combination of filters I try to set within the CALCULATE statement, there are only two results - it keeps the drillthrough filter, or it drops that filter and shows me the entire company and refuses to see any other filters. I feel like I'm overlooking something really obvious just because I've been pounding my head on it so long. Any insight? TIA!
Solved
twintrbl
6 years ago Place DAX Commands and Tips
10KViews
0likes
4Comments
SSAS Tabular - Default Detail Rows Expression
For a SSAS 2017 tabular model surfaced via pivot tables, a handy (vital) feature is the ability to drill through to a sensible selection of detail rows via double click on a measure (just like in the old school Excel pivot tables that your finance department can't part with). The content of the drillthough is determined by the table property [Default Detail Rows Expression]. The example Microsoft provides here is great insofar as it goes. SELECTCOLUMNS( 'Internet Sales', "Customer First Name", RELATED( Customer[Last Name]), "Customer Last Name", RELATED( Customer[First Name]), "Order Date", 'Internet Sales'[Order Date], "Internet Total Sales", [Internet Total Sales] ) My problem is that I have a calendar table with an active relationship with my fact table (let's call this fact column end_date_sk) and an inactive relationship with the same table (let's call this fact column start_date_sk). If I want to return calendar columns from both the inactive and active relationships, what do I do? RELATED() only seems to work for the active relationship.
CrassJoin
7 years ago Place DAX Commands and Tips
3.8KViews
0likes
3Comments