Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Auto-Filter Tables Based on Date in Column

I am still relatively new to Power BI, so please forgive me if this is a simple question; I have been looking for a solution for days and haven't been able to find an answer. This is also my first ti...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous ,

     

    You can modify the paycode at underlined section and create a new measure.

    I have modified the demo, please try it:demo.pbix

     

    Best Regards,

    Link

v-xulin-mstf's avatar
v-xulin-mstf
Community Support
5 years ago

Hi Anonymous 
Create a measure as:

Measure = var _table=SELECTCOLUMNS(CAs,"Employee ID",CAs[Employee ID],"Date started",IF(CAs[Reopened Date]=BLANK(),CAs[Date started],CAs[Reopened Date])) 
return
var _table2=FILTER(GENERATE(_table,LoAs),[Employee ID]=[ID:]&&[Date]>=[Date started]&&[Pay Code]="Leave of Absence")
return
COUNTAX(_table2,[Employee ID])

Here is the demo, please try it: Auto-Filter Tables Based on Date in Column

Best Regards,

Link

Anonymous's avatar
Anonymous
Not applicable
5 years ago

What should I do with the measure? In your example, the card visual that has the measure is returning blank.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Anonymous 

    The measure return the count the leaves of absence after the start date of the corrective action or after the reopen date if there is one.

    The card visual that has the measure is returning blank because there have no data that meets the conditions.

    Please try the demo and check if it works.

    Best Regards,

    Link

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      Okay, I tried adding in some data that met the criteria on Mosby, Ted and Erickson, Marshall and the measure is still showing blank.

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Anonymous 

        I wonder if i get your expected outcome or not.
        For example, Mosby, Ted leaves of absence after date started or reopen date if there is one.Then count the days of leave of absence.
        If it has mistakes, please show me the right.
        Could you share the sample data on format of excel?
        Expected output would help tremendously.

        Best Regards,

        Link