Formula

[JoseGarcia]

Hi there, 

Master measure below does not apply when I select a project.

Count({<[Project Name]>} IF([Physical % Complete] <'100%', [Project Name]))

Is there any other way I can re-formulate this expression so that when I select project  it returns related values?
Thanks a million

Jose

[NitinK7]

try once

IF([Physical % Complete] <'100%', Count({<[Project Name]>} [Project Name] ))

[agigliotti]

Hi @JoseGarcia ,

Maybe this:

Count(  {< [Project Name] = {"=[Physical % Complete] <100%"} >}  [Project Name] )

I hope it can helps.

Best Regards

[JoseGarcia]

@agigliotti, 

expression returned an error. 😞

a bracket somewhere?

Jose

[JoseGarcia]

@NitinK7 , 

Formula is correct but  does not return correct value. 

any ideas?

[agigliotti]

what about [Physical % Complete] field values?

are those numeric values from 1 to 100 ?

is so you have to change the expression as below:

=Count( {< [Project Name] = {"=[Physical % Complete]<100"} >} [Project Name] )

else you can try with:

=Count( {< [Project Name] = {"=[Physical % Complete]<1"} >} [Project Name] )

do you have only one [Physical % Complete]  value for each project?

[JoseGarcia]

@agigliotti , 

I have attached data table for guidance.
Raw data is under Combined tab. 
Both expression did return 0.

Hope the excel file, can provide clarity.

Jose

[agigliotti]

Below you find the script:

load *,
([Project Name] &
ActivityID ) as KEY0;
LOAD [Project Name],
ActivityID,
[Activity Name],
[Remaining Duration],
Finish,
[BL Project Finish],
[Late Finish],
[EPPR (A)],
[ActOwner (A)],
[Physical % Complete],
Variance,
F12,
Actual,
Baseline,
O
FROM
[C:\Users\Andrea\Downloads\Copy of Delivery Bar Chart V19 2-0202021 for QlikSense.xlsx]
(ooxml, embedded labels, table is Combined);

in UI you can use:

=Count( {< KEY0 = {"=[Physical % Complete]<100"} >} DISTINCT [Project Name] )

Best Regards

[JoseGarcia]

@agigliotti ,

followed steps. 
But not getting correct result.

If we take Leeds project, measure should return '105' 

Jose

[agigliotti]

Based on excel data why the result for Leeds project should be 105?

Sorry but I can't understand the logic behind...

Also what is the primary key of that "Combined" excel sheet ?