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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 overview (Without relationship to date table)Correct overview (Without relationship to date table)

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

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

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

 

Thanks!

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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