Date slicer not working (IF value exists on calendar date)

[tatenda24]

Hi everyone, i need help editing  or creating a new 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.(True,false). and also include their "purchase point" (person which they purchased from). 

biggest problem:
I have implimented a measure to do the above, however when place the measure into a visualization, my date slicer seems to fail in slicing the visualization by date .

the measure is as follows:

measure = 

var _cust = MAX(purchase table[customer])

var _date = MAX('calendar table'[Date])

var _temp = CROSSJOIN(SELECTCOLUMNS({_cust},"cust",[Value]),SELECTCOLUMNS({_date},"dt",[Value]))

var _date2 = CALCULATE(MAX(purchase table [purchse_date]),TREATAS(_temp,t1[customer], purchase table [purchse_date]))

return IF(_date2=BLANK(),"false","true")

the relatioinship:

the tables are as shown below :

The resulting visualization table  :

this is the table i show in the final visualisation 

the measure used in this resulting table is:

Help needed

If anyone could assist me with writing a measure that could  make my requirements work so that my date slicer can work with my resulting table id really appreciate it  suggestions are welcome:

[v-zhangti · Accepted solution]

Hi, @tatenda24 

Maybe you can try the following methods.

Measure:

Measure =
IF (
    SELECTEDVALUE ( Purchase[purchase_date] ) <> SELECTEDVALUE ( 'Calendar'[Date] ),
    0,
    1
)

Put Measure in Filters and set it equal to 1.

The date slicer works as shown in the figure.

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

Perhaps this kind of approach would work for you:


Test data:

Here I try to find out if a company has made a pruchase (if there is "Created at" date).

DAX:

Test For Purchase = IF(

countrows(filter(Deals,MAX('Calendar'[Date])=Deals[Created at]))>=1,

TRUE(),FALSE())

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!