Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filter Data based on a specific row value

Hello,   I have a table with a list of milestones for each project in a vertical manner along with the milestone date and status. I want to be able to filter to specific projects from the table bas...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

Based on my research, you could try this formula:

Result = 
CALCULATE (
    COUNTA ( 'Table'[Project_Cd] ),
    FILTER (
        'Table',
        CALCULATE (
            MAX ( 'Table'[Milestone_Status] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Project_Cd] ),
                'Table'[Milestone] = "Project Finalized"
            )
        ) = "Complete"
            && 'Table'[Milestone_Status] = "Complete"
    )
)

Or split it into two measure

Measure = CALCULATE(MAX('Table'[Milestone_Status]),FILTER(ALLEXCEPT('Table','Table'[Project_Cd]),'Table'[Milestone] = "Project Finalized"))
Measure 2 = 
CALCULATE (
    COUNTA ( 'Table'[Project_Cd] ),
    FILTER (
        'Table',
        [Measure] = "Complete"
            && 'Table'[Milestone_Status] = "Complete"
    )
)

Result:

 

Best Regards,

Lin

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you, this gets me half way there actually! I completely forgot about a Milestone Dt slicer as well. So the user would want to see for example how many "Project Finalized" have been completed for Q1 2019. That would mean I will need to limit the query to only pull Projects that have Project Finalized between 1/1 and 3/31. All the other milestones must also be limited by not only the slicer, but their individual Project Finalized dates.  For example, I can't show 5 Project Approveds between Q1 and Q2  even though I have only 2 Project Finalizeds in Q1.  The 2 Project Approveds should be a subset of the 2 Project Finalized for Q1. Is there a way to limit this in DAX? Thanks in advance.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

     For example, I can't show 5 Project Approveds between Q1 and Q2  even though I have only 2 Project Finalizeds in Q1.  The 2 Project Approveds should be a subset of the 2 Project Finalized for Q1. Is there a way to limit this in DAX?

    I'm a little confused about your description?

    Could you explain it with the sample data? what is your expected output and what is your current output?

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

     

    Best Regards,

    Lin