Cannot get my measure to filter

[Anonymous]

Hi Experts

See image of Model

I have created a bridge between STG FACT to_do Closed and and STG FACT to_do Created. Both tables link to the STG Dim_Date Table as follows

STG FACT to_do Closed (Date_Closed) to STG Dim_Date (Date)

STG FACT to_do Created (created_Date) to STG Dim_Date (Date)

I am trying make the following Measure Filter

Not Settled C/F =

VAR _MaxDate = MAX('STG Dim_Date'[FirstDayOfMonth])

RETURN

CALCULATE(SUM('STG Fact_To_Do_Created'[Closed]),

FILTER(ALLEXCEPT('STG Fact_To_Do_Created','STG Fact_To_Do_Created'[Created_By_FullName]),

'STG Fact_To_Do_Created'[Created_Date] >= _MaxDate &&

'STG Fact_To_Do_Created'[Closed] = BLANK() ))

The measure returns a constant value, not a values based on agent name.

Cannot see my error.

 

[edhans · Accepted solution]

Here's your measure:

Not Settled C/F =
VAR _MaxDate =
    MAX( 'STG Dim_Date'[FirstDayOfMonth] )
RETURN
    CALCULATE(
        SUM( 'STG Fact_To_Do_Created'[Closed] ),
        FILTER(
            ALLEXCEPT(
                'STG Fact_To_Do_Created',
                'STG Fact_To_Do_Created'[Created_By_FullName]
            ),
            'STG Fact_To_Do_Created'[Created_Date] >= _MaxDate
                && 'STG Fact_To_Do_Created'[Closed]
                    = BLANK()
        )
    )

The created_by_fullname is not filtering the agent table. You'd need to see more information to drill down, but the first thing you'd try to do is add CROSSFILTER as a calculation modifier.

Not Settled C/F =
VAR _MaxDate =
    MAX( 'STG Dim_Date'[FirstDayOfMonth] )
RETURN
    CALCULATE(
        SUM( 'STG Fact_To_Do_Created'[Closed] ),
        FILTER(
            ALLEXCEPT(
                'STG Fact_To_Do_Created',
                'STG Fact_To_Do_Created'[Created_By_FullName]
            ),
            'STG Fact_To_Do_Created'[Created_Date] >= _MaxDate
                && 'STG Fact_To_Do_Created'[Closed]
                    = BLANK()
        ),
        CROSSFILTER('STG Fact_To_Do_Created'[Agent_name],'Agent Name'[Agent_name],BOTH)
    )

Note that I guessed the relationship fields between the created table and the agent table. Fix it on your side.
Also, in your visual, you're using the fields in the bridge table, right, not any of the STG FACT* tables for the key fields?

[Anonymous]

Hi

the new measure is not filtering....

[edhans]

ok. As noted before, need some data and more info. A PBIX file with no confidential data would be great.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum