Forum Discussion
Helper ISELECTEDVALUE returns value for related dimension, while not present in fact
I am creating the following measure, which has to returns a value from my fact table based on different scopes.
DEFINE
MEASURE 'People Engagement'[Engagement Rotation] =
VAR YearInScope =
ISINSCOPE ( 'Date'[Year] )
VAR QuarterInScope =
ISINSCOPE ( 'Date'[Quarter] )
VAR MonthInScope =
OR ( ISINSCOPE ( 'Date'[Month] ), ISINSCOPE ( 'Date'[YearMonth] ) )
VAR DateInScope =
ISINSCOPE ( 'Date'[Date] )
VAR EvalYear =
IF (
COUNTAX ( 'People Engagement', [Rotation] ) >= 1,
"Expand to Quarter or Month",
BLANK ()
)
VAR EvalQuarter =
LASTNONBLANKVALUE ( 'Date'[Date], SELECTEDVALUE( 'People Engagement'[Rotation] ) )
VAR EvalMonth =
SELECTEDVALUE ( 'People Engagement'[Rotation] )
RETURN
SWITCH (
TRUE (),
DateInScope, BLANK (),
MonthInScope, EvalMonth,
QuarterInScope, EvalQuarter,
YearInScope, EvalYear,
BLANK ()
)
EVALUATE
SUMMARIZECOLUMNS ( 'Employees'[Employee ID], 'Date'[Year], 'Date'[Month], FILTER ( 'Date', 'Date'[YearMonth] IN {202307, 202308, 202309, 202310, 202311, 202312}), "Eval", [Engagement Rotation])
The value that I am returning (Rotation) is a boolean.
I am running into the error that the measure returns FALSE for all values in my dimensions (Employees, Date), even when these values are not in my fact table (People Engagement).
I am using a very similar measure to retun a text field, and this works as intended only returning values in my fact table.
How can I stop the evaluation for values not in my fact table, and does this have to do with the boolean type of the Rotation column?
The issue was resolved by converting the value from BIT to String in the datamodel, such that Power BI would not confuse blank values with FALSE
6 Replies
MAVIEThe issue was resolved by converting the value from BIT to String in the datamodel, such that Power BI would not confuse blank values with FALSE
- MAVIE
Hi Fowmy
There is a many - one relationship between People Engagement and Date, and People Engagment and Employees. When doing a summarizecolumns over Employees and Date, I would therefore expect SELECTEDVALUE( 'People Engagement'[Rotation] ) to be evaluate in the context of these tables, such that I return the Group by coulmns from the SUMMARIZECOLUMNS function which has a value in People Engagment. This works as intentded if I swap the Rotation boolean column for a text column.
Adding 'People Engagement'[Rotation] to SUMMARIZECOLUMNS does not resolve the issue.
Here is an example of the evaluated measure:All of these employees are evaluated to false, despite not having an entry in the People Engagement table
