DAX: Combining EXCEPT() function with USERELATIONSHIP function

[Titatovenaar2]

Hi guys,

I want to be able to view which materials (unique key in table 'DIM Material') have not been moved (movements per material and date in table 'FACT Materials'), by using the EXCEPT() functionality. 

I have 4 tables: DIM Material, DIM MaterialPlant, FACT Materials and Calendar. The Calendar has an inactive relationship with the FACT Materials table (see details in .pbix included).

Currently I use the following DAX which does the job perfectly when considering no selection on the calendar:

Not Moved Materials_2 = 
VAR MatPlant = CALCULATETABLE( 
            VALUES ( 'DIM Material'[%MaterialKey]) )
VAR MatMove = CALCULATETABLE( 
            VALUES ( 'Fact Materials'[%MaterialKey]), 
            'FACT Materials'[Source] = "BF" )

RETURN
CALCULATE(
    COUNT('DIM Material'[%MaterialKey]),
    EXCEPT(
        MatPlant, MatMove
    )--,USERELATIONSHIP('FACT Materials'[DateKey], 'Calendar'[DateKey])
)

It then shows the value '2', which is correct, since there are only 2 materials who have never been moved.

However, as soon as I activate the Calendar relationship and select year 2017, it counts '0', while I expect '5'.

Here's the .pbix, in case necessary!

EXCEPT function .pbix 

Kind regards,

Igor

[v-robertq-msft · Accepted solution]

Hi, @Titatovenaar2 

According to your description and DAX formula, I can understand your requirement clearly. Then I downloaded your pbix file and opened it to change your original measure and achieve your requirement, you can try my measure:

Not Moved Materials1 =

VAR MatPlant =

SELECTCOLUMNS(ALLSELECTED('DIM Material'),"1",[%MaterialKey])

VAR MatMove =

SELECTCOLUMNS(FILTER(ALLSELECTED('FACT Materials'),[Source]="BF"),"1",[%MaterialKey])

var _except=EXCEPT(MatPlant,MatMove)

RETURN

COUNTX(_except,[1])

And I create a card chart to display the new measure, then I can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.