Combine Measures

[chrismcdonald]

Hello,

I am trying to combine 2 measures that both work individually but I can't get them to work when combined. Please help.

The end result should only count the projects that matches the both criteria but also has all the subprojects completed for that project ID.

Example

Project ID	Sub Project	Driver	Install Actual
1	1	Urgent Work	1/01/2020
1	2	Urgent Work
1	2	Urgent Work	1/01/2020
2	1	Urgent Work	2/02/2020
2	2	Urgent Work	2/02/2020
2	2	Urgent Work	2/02/2020

Expected Result (showing only 1 because only 1 project has been fully completed)

Driver	Count
Urgent Work	1

This measure is used to roll up details to a project level. e.g 1 project ID might have multiple sub projects

Install Actual (Project Level) =

IF (

    CALCULATE (

        COUNTBLANK ( Table[Install Actual] ),

        ALLEXCEPT (Table, Table[Project ID] )

    ) > 0,

    BLANK (),

    CALCULATE ( MAX ( Table[Install Actual] ), ALLEXCEPT ( Table, Table[Project ID] ) ))

This measure is use to do a count of the Project ID's based on the filters

Urgent work Completed Last week=

         CALCULATE (

            DISTINCTCOUNT ( Table[Project ID] ),

            FILTER ( Table, Table[Week Index] = "-1" ),

            FILTER ('Table', Table[Driver] = "urgent work"))

[amitchandak · Accepted solution]

@chrismcdonald , With description, I am not clear on missing measure.

The table you want to show, all sub project completed

countx(filter(summarize(Table,table[Project ID],"_1",count(Table[Sub Project]), "_2",countx(filter(Table,not(isblank(table[Install Actual]))),Table[Install Actual])),[_1] =[_2]),table[Project ID])

If you want to work with the week. Please Use date dimension and Week rank like this

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Refer my file

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[chrismcdonald]

Thank you @amitchandak .. I am pretty sure this will work for what I need.

Thank you