Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure filtered by dates

Hello - My measure below returns results as expected until the underlined part. I need to add more filtering with dates that I can't seem to do. I want to be able to say X date (related table) is less than Y date. Thoughts? Thanks!

 

Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),altus_leadstage[altus_name]="MQL" && DATEVALUE(altus_leadstage[altus_Date])<=DATEVALUE(...

 

 

 

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please try this one.

Measure 2 =
VAR d =
    MAX ( Opportunity[CreatedOn] )
RETURN
    CALCULATE (
        COUNT ( Opportunity[OpportunityId] ),
        FILTER (
            altus_leadstage,
            altus_leadstage[altus_name] = "MQL"
                && altus_leadstage[altus_Date] < d
        ),
        USERELATIONSHIP ( Opportunity[ParentContactId], altus_leadstage[altus_Contact] )
    )

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please make your data type of your date columns to be the same and check again.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @Anonymous ,

 

Has your issue been solved? If so kindly mark my answer as a solution to close the case. Thanks in advance. Any other question, feel free to let me know please.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

You should be use like

Table1[Date] = related(Table2[Date])

Or

Table1[Date] =Max(Table2[Date])

 

Please provide the complete clause for more help.

Can you share sample data and sample output.

 


Appreciate your Kudos.

 

Anonymous
Not applicable

Thanks for the response and sorry for my delayed response. Still getting an error...

 

Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),altus_leadstage[altus_name]="MQL" && altus_leadstage[altus_Date]<MAX(Opportunity[CreatedOn]))
 

Capture.PNG

Hi,

Try this measure

Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),FILTER(altus_leadstage,altus_leadstage[altus_name]="MQL"&&altus_leadstage[altus_Date]<MAX(Opportunity[CreatedOn])))

If it does not help, then share the link from where i can download your PBI file.  Clearly show the problematic measure there.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.