Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

I need help for DAX query Static to dynamic

Hello Team,

 

I Created below DAX function for Projects count purpose, its working fine only under but I need Dynamic view. present working in a static view.

 

In my present dashboard have a total of 9 filters, below DAX not reflecting those filters. 

 

Projects =
var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year]))-1
var c= FORMAT(max('Compliance Raw Data'[today]),"DD")
var d = FORMAT(max('Compliance Raw Data'[today]),"YYYY")
var e =CONVERT( CONCATENATE(c&"-",CONCATENATE(a&"-",d)),DATETIME)
return
CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),ALL('Compliance Raw Data'),'Compliance Raw Data'[Project End Date]>=e,'Compliance Raw Data'[iManage Project Status]="A")
 
Sample raw data
 
I need dynamic change for the above mentioned DAX function.
Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    Hi Anonymous ,

     

    Modify your dax as follows:

     

    Projects =
    var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year]))-1
    var c= FORMAT(max('Compliance Raw Data'[today]),"DD")
    var d = FORMAT(max('Compliance Raw Data'[today]),"YYYY")
    var e =CONVERT( CONCATENATE(c&"-",CONCATENATE(a&"-",d)),DATETIME)
    return
    CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),ALLSELECTED('Compliance Raw Data'),'Compliance Raw Data'[Project End Date]>=e,'Compliance Raw Data'[iManage Project Status]="A")
     
    Thanks,
    Pragati

4 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    Modify your dax as follows:

     

    Projects =
    var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year]))-1
    var c= FORMAT(max('Compliance Raw Data'[today]),"DD")
    var d = FORMAT(max('Compliance Raw Data'[today]),"YYYY")
    var e =CONVERT( CONCATENATE(c&"-",CONCATENATE(a&"-",d)),DATETIME)
    return
    CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),ALLSELECTED('Compliance Raw Data'),'Compliance Raw Data'[Project End Date]>=e,'Compliance Raw Data'[iManage Project Status]="A")
     
    Thanks,
    Pragati
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Somewhat it is reflecting now, but when selected last historical month, not reflecting on last month "March" Data

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        What is the error on the card visual? Click on "See Details"

         

        Thanks,

        Pragati

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Pragati,

       

      Somewhat it is reflecting now, but when selected last historical month, not reflecting on last month 

       

      Month "March" not reflecting

       

      Is there any best way to optimize the DAX function. Please help me