How to apply DAX filters to my complex measure?

[Anonymous]

Hi Everyone,

I previously requested help in constructing a measure that would correctly display my subtotals in a matrix visual. This thread can be viewed here.

My issue is I would like to create a new measure based on this that filters by other categories in my table. When I create a table this displays fine. However I would like to show these as a guage chart, when I create a new measure to filter just by specific categories the measure does not display any value. 

My measure is as follows:

Weighted Documentation Status = 
VAR a =
    CALCULATE (
        [Percent Complete by Task Status],
        '13/05/2020 - all tasks'[Task Status] = "Open"
    ) * 0
VAR b =
    CALCULATE (
        [Percent Complete by Task Status],
        '13/05/2020 - all tasks'[Task Status] = "In progress"
    ) * 0.5
VAR c =
    CALCULATE (
        [Percent Complete by Task Status],
        '13/05/2020 - all tasks'[Task Status] = "In Review"
    ) * 0.75
VAR d =
    CALCULATE (
        [Percent Complete by Task Status],
        '13/05/2020 - all tasks'[Task Status] = "Complete"
    ) * 1
VAR e =
    CALCULATE (
        [Percent Complete by Task Status],
        '13/05/2020 - all tasks'[Task Status] = "Rejected"
    ) * 0
RETURN
    IF (
        HASONEVALUE ( '13/05/2020 - all tasks'[Task Status] ),
        SWITCH (
            SELECTEDVALUE ( '13/05/2020 - all tasks'[Task Status] ),
            "Open", a,
            "In progress", b,
            "In Review", c,
            "Complete", d,
            "Rejected", e
        ),
        a + b + c + d + e
    )

I tried to create a filtered version as so:

Engagement Status - Execution = CALCULATE([Weighted Documentation Status],'13/05/2020 - all tasks'[Engagement Phase]="Execution")

However this doesn't work, please see screenshots for results. The table works fine using the original version. I suppose what i would like to get to is have the separate engagement phases as individual measures to use in a gauge chart.

[AllisonKennedy]

What table is the Engagement Status in?

Try:
Engagement Status - Execution = MINX(FILTER(Table, Table[Engagement Phase] = "Execution"), [Weighted Documentation Status])

[Anonymous]

Hi @AllisonKennedy 

That measure doesn't work, it returns a value of 0

Note that I found a solution whereby I put my 1st measure in a gauge chart and apply a filter to the visual via the filters pane. However I am trying to improve my DAX knowledge so it would be helpful to work out a solution all the same!

[AllisonKennedy]

Are you able to provide sample file?

[Anonymous]

Hi @AllisonKennedy 

my data is all company sensitive so I'd rather not. I understand that will make it hard for you to help but I have a working solution so i'll leave this one until my DAX knowledge improves. thank you for taking an interest all the same 🙂

[v-xuding-msft]

Hi @Anonymous ,

Can you please share your workaround here? Others who have the same problem can benefit from this thread.

[Anonymous · Accepted solution]

Spoiler

Hi @v-xuding-msft 

I refer to my work around in my previous post: 

"Note that I found a solution whereby I put my 1st measure in a gauge chart and apply a filter to the visual via the filters pane."