Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
While the result when the relationship is active is incorrect and looks like:
Any ideas how to fix this without de-activating the relationship?
Thanks!
You may add CROSSFILTER Function and set cross-filter direction to None.
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:
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |