topic Re: Date slicer not working (IF value exists on calendar date) in DAX Commands and Tips

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

Anonymous — Wed, 19 Jan 2022 17:05:58 GMT

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:

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

ValtteriN — Wed, 19 Jan 2022 18:22:41 GMT

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!

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

v-zhangti — Mon, 24 Jan 2022 07:00:09 GMT

Hi, Anonymous

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.