Forum Discussion

KNP's avatar
KNP
Super User
4 years ago

Measure with Disconnected Table as Filter

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 ...
Need Help
KNP's avatar
KNP
Super User
4 years ago

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's avatar
AlexisOlson
Super User
4 years ago

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]
    )