Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, i need help creating a measure that evaluates whether a value in on table exists at a particular date within the calender table .
I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates.
the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender.
the tables are as shown below :
RESULT
If a customer has purchased something on the date equivalent to the calendar date, return true, else false. as shown below:
If anyone could assist me with writing a measure that could produce this outcome that would be wonderful 🙂 all help and sugegestions are welcome:
Thank you
Solved! Go to Solution.
Hi,
Something like this should do what you want:
End result (Use calendar for axis):
I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!
Proud to be a Super User!
@tatenda24 you can use a measure like this
Measure =
VAR _cust =
MAX ( t1[customer] )
VAR _date =
MAX ( 'Calendar'[Date] )
VAR _temp =
CROSSJOIN (
SELECTCOLUMNS ( { _cust }, "cust", [Value] ),
SELECTCOLUMNS ( { _date }, "dt", [Value] )
)
VAR _date2 =
CALCULATE (
MAX ( t1[purchse_date] ),
TREATAS ( _temp, t1[customer], t1[purchse_date] )
)
RETURN
IF ( _date2 = BLANK (), "false", "true" )
@tatenda24 you can use a measure like this
Measure =
VAR _cust =
MAX ( t1[customer] )
VAR _date =
MAX ( 'Calendar'[Date] )
VAR _temp =
CROSSJOIN (
SELECTCOLUMNS ( { _cust }, "cust", [Value] ),
SELECTCOLUMNS ( { _date }, "dt", [Value] )
)
VAR _date2 =
CALCULATE (
MAX ( t1[purchse_date] ),
TREATAS ( _temp, t1[customer], t1[purchse_date] )
)
RETURN
IF ( _date2 = BLANK (), "false", "true" )
If the calendar table is related to the purchase date, then all you need is
Measure1 = NOT ISEMPTY ( t1 )
@AlexisOlson OP did not mention anyhting about the data model, so my starting point was unrelated tables. Thanks again !!!
Yeah, without the relationship, it needs a TREATAS or similar application of the date filter.
CALCULATE (
NOT ISEMPTY ( t1 ),
TREATAS ( VALUES ( 'Calendar'[Date] ), t1[purchse_date] )
)
Hi,
Something like this should do what you want:
End result (Use calendar for axis):
I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |