PowerBIDax: How to Group $Values by Opportunity then Apply Filters From Direct Query Connection? - Stack Overflow

long time lurker first time poster.

I'm attempting to implement some manual reports through dataflows via PowerBI. The dataflow provides a list of "OpportunityID", "LineNumber", & "LineValue". The outcome I'm looking for is to combine the value of all line items by "OpportunityID". Then I want to look at "OpportunityID"s that are > 1,500 in value. There is one more filter that is related to time where I want to look lines created in the last rolling 12 months, I may have this one figured out though I'm curious what others may recommend.

I'm relatively new to PowerBI/Dax and some of the tools I'd use in the PowerQuery aren't available because this is a DirectQuery connection.

So far I've tried using group by and some other methods from the Microsoft website, stackoverflow, and any others Google can come up with.

Found a YouTube tutorial that seemed to cover the topic however when I attempted to replicate and modify for a count I didn't get the filter by "OpportunityID" that I expected.

TotalOrderSales = 
var CurrentOrderNumber = CRM_OPPORTUNITIES[OpportunityID] <-This line doesn't work
var TotalOrder =
calculate (
SUM(CRM_OPPORTUNITIES [LineValue]),
ALL(CRM_OPPORTUNITIES),
CRM_OPPORTUNITIES[OpportunityID] = CRM_OPPORTUNITIES[OpportunityID])
var SalesExclNeg = 
IF(
TotalOrder > 1500,
DISTINCTCOUNT(CRM_OPPORTUNITIES[OpportunityID]),
0
> )
RETURN
SalesExclNeg

A coworker suggested a formula and it to errored out after some attempted modifications.

UniqueOpportunitiesOverThreshold = 
VAR RollingPeriod =
    DATESINPERIOD(
        CRM_OPPORTUNITIES[Creation Date/Time],
        MAX(CRM_OPPORTUNITIES[Creation Date/Time]),
        -12,
        MONTH
        )

VAR FilteredOpportunities =
    FILTER(
        VALUES(CRM_OPPORTUNITIES[Opportunity ID]),
        CALCULATE(
            SUM(CRM_OPPORTUNITIES[LineValue]),
            RollingPeriod
        ) >= 15000
    )
RETURN
    DISTINCTCOUNT(FilteredOpportunities)

The DISTINCTCOUNT function only accepts a column reference as an argument.

Here is an example of the dataset I'm using:

My expectation is that the results would show opportunity 10010 & 10011 as those are the only 2 that have a total value >= 1500

Thank you in advance for anyone willing to share their time and expertise.