Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere'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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi
the new measure is not filtering....
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |