Measure with Disconnected Table as Filter

[KNP]

TLDR

I suspect this will be easy for someone with a better understanding of DAX (currently my weakest skill set, work in progress) and that hasn't been staring at the problem for hours.

This measure works...

LeaveDaysDynamic = 
    SUMX(
        FILTER(
            VALUES(EMLAC[DET_NUMBER]),
                 [Leave Days] >= MIN(DaysGroup[MinValue]) 
                 && [Leave Days] < MAX(DaysGroup[MaxValue])
        ),
        [Leave Days]
    )

This one doesn't...

LeaveSummaryDaysDynamic = 
    SUMX(
        FILTER(
            VALUES( LeaveSummary),
                 [Leave Summary Days] >= MIN(DaysGroup[MinValue]) 
                 && [Leave Summary Days] < MAX(DaysGroup[MaxValue])
        ),
        [Leave Summary Days]
    )

I need to understand what VALUES(EMLAC[DET_NUMBER]) vs VALUES( LeaveSummary) is doing and what I should use to get it to work.

Data Model (partial)

DaysGroup is the disconnected table that I'm using to create bins in the report.

LeaveSummary is an aggregated table from EMLAC with some columns removed so I can show data irrespective of the RLS.

Visual

The grey bars should be showing the same as the blue bars in this image as there is no Location filter selected.

Measures

Working

Leave Days = SUM(EMLAC[Total Days])

LeaveDaysDynamic = 
    SUMX(
        FILTER(
            VALUES(EMLAC[DET_NUMBER]),
                 [Leave Days] >= MIN(DaysGroup[MinValue]) 
                 && [Leave Days] < MAX(DaysGroup[MaxValue])
        ),
        [Leave Days]
    )

Total Leave Days for Location = 
CALCULATE(
	[LeaveDaysDynamic],
	ALL(DaysGroup[Days Group],  DaysGroup[Days Group Sort Order])
)

Leave Days % = DIVIDE([LeaveDaysDynamic], [Total Leave Days for Location])

Leave Summary Days = SUM(LeaveSummary[Total Days])

Not Working

LeaveSummaryDaysDynamic = 
    SUMX(
        FILTER(
            VALUES( LeaveSummary),
                 [Leave Summary Days] >= MIN(DaysGroup[MinValue]) 
                 && [Leave Summary Days] < MAX(DaysGroup[MaxValue])
        ),
        [Leave Summary Days]
    )

I suspect this will work fine once LeaveSummaryDaysDynamic is fixed.

Leave Days % (All Locations) = 
    // IF(
    //     ISFILTERED(EMPOS[GNA_ORG_NAME]),
            DIVIDE(
                [LeaveSummaryDaysDynamic]
                , [Leave Days (Grand Total)])
            // , BLANK()
    // )

Sanitised Data

I've given cleaned data samples below. I think that is everything that is needed to solve this.

EMLAC 

DET_NUMBER	LAC_LVE_TYPE	Total Days
1	ALT	3
1	ANN	49.55825394
1	ANN	0
1	LSL	4.00000241
2	ALT	-1.57142857
2	ANN	26.72894555
2	ANN	0
2	LSL	1.14632565
3	ALT	0
3	ANN	9.05906
3	ANN	0
3	LSL	2.00000164
4	ALT	0
4	ANN	83.54460893
4	ANN	0
4	LSL	0
5	ALT	0
5	ANN	27.50526516
5	ANN	0
5	LSL	15
6	ALT	1.11111111
6	ANN	37.03696825
6	ANN	0
6	LSL	0
7	ALT	0
7	ANN	59.54109603
7	ANN	0
7	LSL	0
8	ALT	1
8	ANN	4.00456556
8	ANN	0
8	LSL	1.99999826

LeaveSummary

GNA_ORG_NAME	LAC_LVE_TYPE	Total Days
a	ALT	0
a	ANN	59.54109603
a	LSL	0
b	ALT	0
b	ANN	83.54460893
b	LSL	0
c	ALT	1.11111111
c	ANN	37.03696825
c	LSL	0
d	ALT	1.42857143
d	ANN	85.34625949
d	LSL	7.1463297
e	ALT	0
e	ANN	27.50526516
e	LSL	15
f	ALT	1
f	ANN	4.00456556
f	LSL	1.99999826

DaysGroup

Days Group	Days Group Sort Order	MinValue	MaxValue
< 20	10	-1000	20
20-30	20	20	30
30-40	30	30	40
40-50	40	40	50
50-60	50	50	60
60-70	60	60	70
70-80	70	70	80
80-90	80	80	90
90-100	90	90	100
> 100	100	100	1000

[KNP]

Thanks for the replies.

I finally got to the bottom of it. Source data that was supposed to be unique in the source system was not. Story of my life. 

Once I cleansed that, my original, as well as all the suggested measures also worked. I should have gone back to the source from the start.

Apologies for wasting your time.

Since I have your attention.

Assuming every row in the table is unique, is there a "best" or most performant way to write those measures?

e.g. SUMX(FILTER(VALUES(Table[Column])... or SUMX(FILTER(VALUES(Table), ... or SUMX(SUMMARIZE... another pattern?

[AlexisOlson]

The basic pattern seems fine but you can probably avoid recomputing the same values multiple times with some variables. For example,

LeaveDaysDynamic =
VAR _Min = MIN ( DaysGroup[MinValue] )
VAR _Max = MAX ( DaysGroup[MaxValue] )
VAR _CalcDays =
    ADDCOLUMNS ( VALUES ( EMLAC[DET_NUMBER] ), "@Days", [Leave Days] )
RETURN
    SUMX ( 
        FILTER ( _CalcDays, [@Days] >= _Min && [@Days] < _Max ),
        [@Days]
    )

[AlexisOlson]

I've attempted to reproduce your issue given the data you've provided but I'm struggling a bit.

Can you modify the file I created or create your own version that you can share?

[parry2k]

@KNP if you see the diagram below, based on your sample data, In the red box, a new measure I provided, yellow highlighted your original measure, and Card visual showing total summary days and total matches everywhere. Something else is going on with your real model since you mentioned there are a lot more columns in the table, if you trim down your table to just 3 columns that you gave in the example and then test the measure, what do you get?

it is hard to debug what is going on with partial information

[parry2k]

@KNP try this measure, let me know if it works, I guess you want to iterate over each unique row based on GNA_ORG_NAME & LAV_LVE_TYPE  to get the Total Days:

Leave Summary sample data you provided is not good enough to produce the issue because each row in the sample data is unique.

LeaveSummaryDaysDynamic = 

    SUMX ( 
        SUMMARIZE ( 
            LeaveSummary,LeaveSummary[GNA_ORG_NAME],LeaveSummary[LAC_LVE_TYPE], "@LeaveSummaryDays", [Leave Summary Days] 
        ),
        IF ( 
            [@LeaveSummaryDays] >= MIN ( DaysGroup[MinValue] ) && 
            [@LeaveSummaryDays] < MAX ( DaysGroup[MaxValue] ), 
            [@LeaveSummaryDays] 
        )
    ) 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[KNP]

@parry2k - Your assumptions are correct. I was hopeful when I saw your measure but unfortunately it is still returning incorrect numbers. I would be suprised if the actual table rows weren't unique. 

I can't get my head around why the total in the >100 days is so high (and wrong). 

[jennratten]

Hello - when a table is used in the VALUES function a table with the same columns is returned.  When a column is used in the VALUES function a single column table is returned.  LeaveDaysDynamic is calculating the leave days for each unique value from the [DET_NUMBER] of the EMLAC table with the min/max specified whereas LeaveSummaryDaysDynamic is being fed a table with multiple columns as it's basis.  Is the DET_NUMBER field also available on the summary table?  If so, you can use it inside VALUES. 

Documentation

https://docs.microsoft.com/en-us/dax/values-function-dax 

Return value

When the input parameter is a column name, a single column table. When the input parameter is a table name, a table of the same columns is returned.

Remarks

When you use the VALUES function in a context that has been filtered, the unique values returned by VALUES are affected by the filter. For example, if you filter by Region, and return a list of the values for City, the list will include only those cities in the regions permitted by the filter. To return all of the cities, regardless of existing filters, you must use the ALL function to remove filters from the table. The second example demonstrates use of ALL with VALUES.

This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

For best practices when using VALUES, see Use SELECTEDVALUE instead of VALUES.

[SanjeevKumar]

The Syntax for values is we need to use either table name Or column name. 

VALUES ( TableNameOrColumnName) 

In your DAX you must use the column as 

VALUES(LeaveSummary

[GNA_ORG_NAME])

Please use the below function and try. 

LeaveSummaryDaysDynamic = 

    SUMX(

        FILTER(

            VALUES(LeaveSummary

[GNA_ORG_NAME]),

                 [Leave Summary Days] >= MIN(DaysGroup[MinValue]) 

                 && [Leave Summary Days] < MAX(DaysGroup[MaxValue])

        ),

        [Leave Summary Days]

    )

Please mark it as solved If it works. 

[KNP]

Unfortunately that doesn't help. 

This is the incorrect data currently...

This is with your suggested modification...

Should match the LeaveDaysDynamic column as there are no filters applied.

Any other ideas?

[bcdobbs]

Can you explain the the relationship between 

EMLAC[DET_NUMBER]

and the columns in leave summary?

Further more are there more columns in leave summary that aren't in your sample data.

I think the issue comes from the automatic context transition caused by your measure. It works fine on your first example because you're iterating over unique values. On the one that doesn't work you're iterating over a table rather than a column and don't necessarily have unique rows which causes issues when the row context moves into the filter context.

[KNP]

@bcdobbs - DET_NUMBER is unique employee number and is not in the summary table. Yes there are more columns in the summary table (sensitive data).

Context switching hurts my brain at this time of the week. DAX context switching that is, I'd be happy to switch context to the weekend. 😂

[bcdobbs]

LeaveSummaryDaysDynamic = 
    SUMX(
        FILTER(
            VALUES( LeaveSummary),
                 [Leave Summary Days] >= MIN(DaysGroup[MinValue]) 
                 && [Leave Summary Days] < MAX(DaysGroup[MaxValue])
        ),
        [Leave Summary Days]
    )

Ok so as a starting point to fix it is to consider what column(s) uniquely define the set of rows you want to add up in [Leave Summary Days] inside the filter.