cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tatenda24
Frequent Visitor

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

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:

model.PNG
the tables are as shown below :

 

Screen Shot 2022-01-19 at 10.35.31 AM.pngScreen Shot 2022-01-19 at 10.30.47 AM.png

 

The resulting visualization table  :

this is the table i show in the final visualisation 

 

the measure used in this resulting table is:


Screen Shot 2022-01-19 at 10.44.14 AM.png

 

 

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:

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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.

vzhangti_0-1643007403384.png

The date slicer works as shown in the figure.

vzhangti_1-1643007458696.png

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.

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

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.

vzhangti_0-1643007403384.png

The date slicer works as shown in the figure.

vzhangti_1-1643007458696.png

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
Super User
Super User

Hi,

Perhaps this kind of approach would work for you:


Test data:

ValtteriN_0-1642616470645.png



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:
ValtteriN_1-1642616552154.png

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!









Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!