Measure to know if Date matches in inactive relationship

[Anonymous]

Hi all

I have 2 tables. A date table created with the calendar function in Power BI:

DateTable = CALENDAR("01.01." & YEAR(TODAY())-4; "31.12." & YEAR(TODAY()) + 1)

And table orders. In orders there is the column "OfferDate".

In my datetable i need a column that tells me if on the date were an order offered.

The tables are in an inactive relationship, so i know i need to use "Userelationship" but i dont know how to get a bool based on the date.

Example:

 

Many thanks

[jdbuchanan71]

Hello @Anonymous 

If you are wanting to count the number of lines in the orders table base on the offer date it would be.

Offers = 
CALCULATE(
    COUNTROWS( Orders ),
    USERELATIONSHIP ( Orders[Offer Date], Dates[Date] )
)

If you are just wanting to tag a date if it has at least one offer date in the order table it will be something like

Has Offer = 
VAR Offers = CALCULATE(COUNTROWS(Orders),USERELATIONSHIP(Orders[Offer Date],Dates[Date]))
RETURN IF ( ISBLANK ( Offers ), "N", "Y" )

[Anonymous · Accepted solution]

Got it to work by myself.

FYI

IsOffered = CALCULATE(IF(Max('Orders'[OfferDate]) <> BLANK(); "3");USERELATIONSHIP(DateTable[Date]; 'ssbi Orders'[OfferDate]))