Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Titatovenaar2
Helper III
Helper III

DAX: Combining EXCEPT() function with USERELATIONSHIP function

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

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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.

v-robertq-msft_0-1618991511079.png

v-robertq-msft_1-1618991511080.png

 

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.

View solution in original post

1 REPLY 1
v-robertq-msft
Community Support
Community Support

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.

v-robertq-msft_0-1618991511079.png

v-robertq-msft_1-1618991511080.png

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.