sum rows filtering on latest date-time with repeated entries and status column

[andrea_chiappo]

What I'm doin is pretty straightfarward: count all completed projects

I have a FactProjects table containing all facts for each project. 

This contains, amongst others, projectID, StatusValues and Date-time stamp columns

Here's what I'm doing:

DISTINCTCOUNT the projectID entries, filtering based on the latest time entry and

on a value of a status column.

In formulas:

CompletedProjects = CALCULATE(

DISTINCTCOUNT(DimProjects[projectID]),

FILTER(FactProjects, CONTAINSSTRING([LastProjectStatus], "Completed"))

)

where

LastProjectStatus =

VAR LASTSTATUS = LOOKUPVALUE(

FactProjects[Statuses.projectStatusValue],

FactProjects[createdDateTime],

MAX(FactProjects[createdDateTime])

)

RETURN IF(ISBLANK(LASTSTATUS), "NO-STATUS", LASTSTATUS)

I upload a screenshot of some sample data. 

Here's the baffling thing:

the measure LastProjectStatus works correctly when selecting (via slicer) projectID=1_17,

but it returns an error for projectID=1_16, despite both having repeated entries in the datetime column

For either projectID, the measure CompletedProjects returns an error. The error is always the following:

MdxScript(Model) (41, 34) Calculation error in measure 'FactProjects'[LastProjectStatus]:

A table of multiple values was supplied where a single value was expected.

I honestly can't get my head around to understand where and why this is failing.

I have read replies to other similar questions, but couldn't find a working answer.

Can anyone kindly provide some helpful insight? Thanks

[v-lid-msft]

Hi @andrea_chiappo ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @andrea_chiappo ,

If the LOOKUPVALUE get multi possible values, it will cause an error, We can use the following measure that use values function to meet your requirement.

LastProjectStatus = 
VAR x =
    CALCULATE (
        VALUES ( FactProjects[Statuses.projectStatusValue] ),
        FILTER (
            FactProjects,
            FactProjects[createdDateTime] = MAX ( FactProjects[createdDateTime] )
        )
    )
RETURN
IF ( ISBLANK ( x ), "NO-STATUS", x )

Then we can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

[MFelix]

Hi @andrea_chiappo ,

Can you share some more information about your data?

You present the formular and the you refer several columns but on your data there is only 2 columns for each project so it's difficult to pin point the error.

Also how is the relationship between your tables?

If you can share a sample file would be great to assist you better.

One more question does you Status Column as a state of Completed or is it Completed and some other text? Why are you using Contains and  lookup to calculate your values? depending on how the model is setup you may only need a simply FILTER or a COUNTROWS with a SUMMARIZE to get the expected result.

Using lookup will for sure return more than one value in some situations.