cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Cannot get my measure to filter

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.
 
1600966639197984824043957234541.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Anonymous
Not applicable

Hi

the new measure is not filtering....

Highlighted

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors