cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
troyel Advocate II
Advocate II

DAX count orders open by date (how to ignore relationship?)

Hi,
I am working on creating a graph showing the number of orders open in each month.
 
I have four relevant fields:
WO_NO | Registered date | Close date | Status
 
The aim is to create a measure that shows each month the last 12 months and count how many orders were open each month (and then color them by status through the legend). And of course not counting any orders that were not yet started or that were closed last month.
 
I managed to find the solution through a DAX measure - however when moving to the model in production there is a relationship between "Registered date" and the date table which removes the functionality by only showing the month the individual order was registered and its status, but not re-counting it for each other month according to the grap axis. I have tried using ALL() to remove this relationship but I don't manage to get it to work with using the date filter in the graph.

My dax that works WITHOUT the relationship is: 

MyOrders = 
CALCULATE (
    DISTINCTCOUNT ( Ark2[WO_NO] );
        FILTER (
            Ark2;
        Ark2[REG_DATE]
            <= CALCULATE ( MAX ( Tabell1[Date] ) )
    );
    FILTER (
        Ark2;
        Ark2[CLOSE_DATE] >= CALCULATE ( MIN ( Tabell1[Date] ))  || 
                Ark2[CLOSE_DATE] = CALCULATE( BLANK() )
    )
)


The result I want looks like: 
Correct.PNGCorrect overview (Without relationship to date table)

 
While the result when the relationship is active is incorrect and looks like:

Wrong.PNGWrong overview (with active relationship to date table)

 
Any ideas how to fix this without de-activating the relationship?

 

Thanks!

2 REPLIES 2
Community Support
Community Support

Re: DAX count orders open by date (how to ignore relationship?)

@troyel,

 

You may add CROSSFILTER Function and set cross-filter direction to None.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
troyel Advocate II
Advocate II

Re: DAX count orders open by date (how to ignore relationship?)

Cool I was not aware of the CROSSFILTER function. Unfortunately it does not seem to work. As I tried to both add it to the original measure as well as adding it as a separate CALCULATE measure. 

OrdersNoCross = CALCULATE([MyOrders];CROSSFILTER(Tabell1[Date];Ark2[REG_DATE];None))

Gives the following result:

incorrect2.PNG
Also tried adding it to the original CALCULATE expression, however that seems to do nothing:

MyOrders = 
CALCULATE ( 
    DISTINCTCOUNT ( Ark2[WO_NO] );
        CROSSFILTER(Ark2[REG_DATE];Tabell1[Date];None);
        FILTER (
            Ark2;
        Ark2[REG_DATE]
            <= CALCULATE ( MAX ( Tabell1[Date] ) )
    );
    FILTER (
        ARK2;
        Ark2[CLOSE_DATE] >= CALCULATE ( MIN ( Tabell1[Date] ))  || 
                Ark2[CLOSE_DATE] = CALCULATE( BLANK() )
    ))


Other suggestions? Thanks! 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors