Forum Discussion

RogueTrooper's avatar
RogueTrooper
Regular Visitor
4 years ago
Solved

How to create second filter

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's avatar
    v-yanjiang-msft
    4 years ago

    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.

2 Replies

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago

    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()
    )

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.