How to create second filter

[RogueTrooper]

Hi.

I have an issue with adding a second filter to my formula.

Before I added the second filter, the formula was error free.

ALL OS VALUE PERIOD = CALCULATE(SUM(MNSalesOrderLine[OS LCY Value]),USERELATIONSHIP(MNSalesOrderHdr[No],MNSalesOrderLine[Document_No]),filter(MNSalesOrderLine,MNSalesOrderLine[Shipment_Date] <=EOMONTH(MAX(LastRefreshedDate[DateLastRefreshed]),0) && MNSalesOrderHdr[Status] = "RELEASED"))

There are three entries within the 'status' field, hence my requirement to select 'RELEASED'.

Error reported, "A single value for column "Status" in table MNSalesOrderHdr cannot be determined.

Any thoughts?

Regards

Wayne

[v-yanjiang-msft · Accepted solution]

Hi @RogueTrooper ,

According to your description, in your formula, in the FILTER function, you can only directly reference columns in the current table or other measures. When you enter a single quote, it automatically pops up which can only be quoted like below:

I create a sample.

MNSalesOrderLine table:

MNSalesOrderHdr table:

LastRefreshedDate table:

Modify the measure like this:

ALL OS VALUE PERIOD =
CALCULATE (
    SUM ( MNSalesOrderLine[OS LCY Value] ),
    USERELATIONSHIP ( MNSalesOrderHdr[No], MNSalesOrderLine[Document_No] ),
    FILTER (
        MNSalesOrderLine,
        MNSalesOrderLine[Shipment_Date]
            <= EOMONTH ( MAX ( LastRefreshedDate[DateLastRefreshed] ), 0 )
            && MAXX (
                FILTER (
                    'MNSalesOrderHdr',
                    'MNSalesOrderHdr'[No] = EARLIER ( 'MNSalesOrderLine'[Document_No] )
                ),
                'MNSalesOrderHdr'[Status]
            ) = "RELEASED"
    )
)

 Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vapid128]

ALL OS VALUE PERIOD =
CALCULATE(
SUM(MNSalesOrderLine[OS LCY Value]),
USERELATIONSHIP(
MNSalesOrderHdr[No],
MNSalesOrderLine[Document_No]
),
filter(
MNSalesOrderLine,
MNSalesOrderLine[Shipment_Date] <=EOMONTH(MAX(LastRefreshedDate[DateLastRefreshed]),0) && MNSalesOrderHdr[Status] = "RELEASED"
)
)

that table name is incorrect.

Do you mean:

ALL OS VALUE PERIOD =
If(
MNSalesOrderHdr[Status] = "RELEASED",
CALCULATE(
SUM(MNSalesOrderLine[OS LCY Value]),
USERELATIONSHIP(
MNSalesOrderHdr[No],
MNSalesOrderLine[Document_No]
),
filter(
MNSalesOrderLine,
MNSalesOrderLine[Shipment_Date] <=EOMONTH(MAX(LastRefreshedDate[DateLastRefreshed]),0)
)
),
blank()
)