Measure

Hello HARSUE,

i think the point is understanding definitions. 

You wrote:

"I want ot allocate the orders to the date (year) where the quotation has been made"

Now in Power BI relationships propagate filters, so what you are doing calling USERELATIONSHIP is defining the year in calendar to mean "the year of the FCT_Offertes[StartDatum] column" for that measure

So when you select a year on a silcer (or on the row of a matrix of a given year), you should see with the measure the amount of orders with StartDatum in the currently selected period, plus the additional conditions you set in the measure (ArticlGroup and type of order) and external other slicers, nothing else is being considered.

It seems to me you need an additional filter to inject in the measure, to clean it up from orders than are in line with the above conditions, but you do not want them in your result set, am I correct?

I am starting from your original code:

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi HARSUE 

USERELATIONSHIP('Date'[Date], FCT_Offertes[StartDatum]) only activates the date filtering on Offers table and SUM is coming from FCT_Orders.

Can you please check this measure and confirm
Orderbedrag uit offertes =
CALCULATE(SUM(FCT_Orders[Bedrag_Excl_BTW]),
FCT_Orders[Artikelgroep] > 99,
NOT ISBLANK(FCT_Orders[OfferteNr]),
TREATAS(VALUES(FCT_Offertes[OfferteNr]),
FCT_Orders[OfferteNr]))

Ensure you have a relationship between these tables FCT_Offertes[OfferteNr] (1) to FCT_Orders[OfferteNr] (M)

Hi HARSUE  can you pls share the sample file to review the modeling part?

Hi HARSUE,

Thank you for reaching out to Microsoft Fabric Community.

Thank you cengizhanarslan, FBergamaschi, krishnakanth240 and techies for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

hi HARSUE ,

How are FCT_Offertes and FCT_Orders related, or who is on the one side?

Hi HARSUE , you are very close.

USERELATIONSHIP activates an inactive relationship, but it does not deactivate any active relationships.

So, if you currently have an active relationship between Date and FCT_Orders (by OrderDate) and you use USERELATIONSHIP to also relate Date to FCT_Offertes[StartDatum], then:

Date will filter both FCT_Orders by OrderDate and FCT_Offertes by StartDatum.
Through the active OfferteNr relationship, FCT_Offertes then filters FCT_Orders as well.

Assuming your active relationship is between 'Date'[Date] and FCT_Orders[OrderDate] (replace OrderDate with your actual date column), you can explicitly disable that relationship inside the measure:

Orderbedrag uit offertes :=
CALCULATE(
SUM ( FCT_Orders[Bedrag_Excl_BTW] ),
FCT_Orders[OfferteNr] <> BLANK(),
FCT_Orders[Artikelgroep] > 99,
-- Use the Date -> Offertes relationship for the quotation date
USERELATIONSHIP ( 'Date'[Date], FCT_Offertes[StartDatum] ),
-- Disable the Date -> Orders relationship for this calculation
CROSSFILTER ( 'Date'[Date], FCT_Orders[OrderDate], NONE )
)

If your table visual is already built on FCT_Offertes (and the Date table is related to FCT_Offertes[StartDatum]), you can also drive the filter from the quotations directly, using VALUES and TREATAS.
Example:

Orderbedrag uit offertes :=
VAR QuotesInContext =
VALUES ( FCT_Offertes[OfferteNr] )
RETURN
CALCULATE(
SUM ( FCT_Orders[Bedrag_Excl_BTW] ),
FCT_Orders[OfferteNr] <> BLANK(),
FCT_Orders[Artikelgroep] > 99,
-- Keep only orders that belong to the quotations in the current context
TREATAS ( QuotesInContext, FCT_Orders[OfferteNr] )
)
 

If this response was helpful in any way, I’d gladly accept a ‌‌much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop ‌‌.

Please try the formula below:

Orderbedrag uit offertes =
VAR OffertesInPeriode =
    CALCULATETABLE (
        VALUES ( FCT_Offertes[OfferteNr] ),
        USERELATIONSHIP ( 'Date'[Date], FCT_Offertes[StartDatum] )
    )
RETURN
CALCULATE (
    SUM ( FCT_Orders[Bedrag_Excl_BTW] ),
    FCT_Orders[Artikelgroep] > 99,
    NOT ISBLANK ( FCT_Orders[OfferteNr] ),
    TREATAS ( OffertesInPeriode, FCT_Orders[OfferteNr] )
)