Help with ignoring row context

Question

Hi!&nbsp;I have two tables; One dimension with accounting codes and one fact table with transactions.&nbsp;I did an easy sum on the transactions like this:&nbsp;&nbsp;Total = SUM(Fact_table[AccountingAmount])&nbsp;But I want to show all accounts, even if there are no transactions. This is the measure that I created:&nbsp;Total_all_accounts = IF (&nbsp; &nbsp; [Total] = BLANK (),&nbsp; &nbsp; 0 + 'Measures Table'[Total],&nbsp; &nbsp; [Total])&nbsp;This is the result, and also the result i wanted. So far so good:But it is now when things are starting to get tricky for me. I want the sum of a specific range of accounts to be summarized in account no 1099. First I did a measure to define the accounts that should be included (1010-1098). Since the data type of the Account is text I used FORMAT:&nbsp;1099 = CALCULATE( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Total_all_accounts],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(DimAccounting, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DimAccounting[Account] &gt;= FORMAT(1010, "Fixed")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; DimAccounting[Account]&nbsp;&lt;= FORMAT(1098, "Fixed")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;When I use the measure in a card visualization I get the result I want. Then I tried to put it all together. I want the measure to display&nbsp;[Total_all_accounts] on every row except for the account 1099, where the measure&nbsp;[1099]&nbsp;should be displayed. Here is the measure I created :&nbsp;Total_test = VAR Account =&nbsp; &nbsp;SELECTEDVALUE (DimAccounting[Account])RETURN&nbsp; &nbsp; SWITCH (&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Account,&nbsp; &nbsp; &nbsp; &nbsp; "1099",&nbsp; &nbsp; &nbsp; &nbsp; [1099]&nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; [Total_all_accounts]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp;And here is the result:[Total_test] give me the same result as&nbsp;[Total_all_accounts], but I want the sum of accounts 1010-1098 on the 1099 account. My best guess is that's because of row context. I hope someone can help me with my issue and show me how to ignore row context on 1099 (or if I made some other misstakes in the previous steps).&nbsp;&nbsp;Thanks!&nbsp;Best regards

anonymous · Accepted Answer

Hi&nbsp;DaveAng&nbsp;,
&nbsp;
Please try.
Total_all_accounts = [Total] + 01099 =
SUMX (
    FILTER (
        ALL ( 'Dim_Table' ),
        'Dim_Table'[Account] &gt;= "1010"
            &amp;&amp; 'Dim_Table'[Account] &lt;= "1098"
    ),
    [Total_all_accounts]
)Total_test = 
SWITCH(
    SELECTEDVALUE('Dim_Table'[Account]),
    "1099",
    [1099],
    [Total_all_accounts]
)
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Help with ignoring row context

2 Replies

Related content

DAX Ignore filter context

Ignore Row Context in Measure

Power BI Copilot ignores barcode scanner context filter in Mobile App

Ignoring row context with DAX

Summarize while ignoring context