topic Re: SELECTEDVALUE returns value for related dimension, while not present in fact in DAX Commands and Tips

SELECTEDVALUE returns value for related dimension, while not present in fact

MAVIE — Wed, 13 Dec 2023 11:16:01 GMT

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?

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

Fowmy — Wed, 13 Dec 2023 11:35:54 GMT

MAVIE

SELECTEDVALUE( 'People Engagement'[Rotation] ) is used in in your measure, so where is it coming from in the filter context?

Modify your SUMMARIZECOLUMNS to include the 'People Engagement'[Rotation] field as well

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

MAVIE — Wed, 13 Dec 2023 12:13:30 GMT

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

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

Fowmy — Wed, 13 Dec 2023 12:27:09 GMT

MAVIE

If you could share a PBI file with dummy/sample data.

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

MAVIE — Wed, 13 Dec 2023 13:43:15 GMT

Fowmy It seems that I do not have permission to upload a file to the post, and I am not able to upload it to a public file sharing platform

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

Fowmy — Wed, 13 Dec 2023 13:45:08 GMT

MAVIE

You may email me. address: fowmy@excelfort.com

Re: SELECTEDVALUE returns value for related dimension, while not present in fact

MAVIE — Tue, 19 Dec 2023 11:47:26 GMT

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