Forum Discussion
Solved
Quickly DAX measure fix - Filter
Hi,
I want to slightly modify my measure.
Currently, it looks like that:
Aging =
VAR ItemID = SELECTEDVALUE(WH_Invent_Trans[ItemID])
RETURN
DATEDIFF(MAXX(
FILTER(ALL(WH_Invent_Trans); WH_Invent_Trans[ItemID] = ItemID);
WH_Invent_Trans[Date Physical]);
TODAY()-1;DAY)
But I want add to this measure 2 conditions.
Firstly, It must be only TransType = 0 or = 9 (transtype is column in table WH_Invent_Trans with values from 0 to 9)
Secondly, QTY<>BLANK (it also column in table WH_Invent_Trans)
I would appreciate any ideas 🙂
Firstly, It must be only TransType = 0 or = 9 (transtype is column in table WH_Invent_Trans with values from 0 to 9)
Secondly, QTY<>BLANK (it also column in table WH_Invent_Trans)
I would appreciate any ideas 🙂
31 Replies
Hi pawelj795
Try something like this.
Aging = VAR ItemID = SELECTEDVALUE( WH_Invent_Trans[ItemID] ) RETURN DATEDIFF( MAXX( FILTER( ALL( WH_Invent_Trans ); WH_Invent_Trans[ItemID] = ItemID && WH_Invent_Trans[TransType] IN { 0, 9 } && WH_Invent_Trans[QTY] <> BLANK ); WH_Invent_Trans[Date Physical] ); TODAY()-1; DAY )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.That works, thanks 😉
Now, I want to develop this measure.Groups = VAR ItemID = SELECTEDVALUE(WH_Invent_Trans[ItemID]) VAR DateDifference = DATEDIFF( MAXX( FILTER(WH_Invent_Trans; WH_Invent_Trans[ItemID]=ItemID && WH_Invent_Trans[TransType] IN {0;9} && WH_Invent_Trans[QTY] <> BLANK() ); WH_Invent_Trans[Date Physical]); TODAY()-1; DAY) RETURN CALCULATE( SUM( WH_Invent_Trans[Inventory Value EUR]); DATESYTD(DimDates[Date]); DateDifference > 0 && DateDifference<= 15)
But it doesn't work.
It shows:
the true/false expression does not specify a column. Each true/false expressions used as table filter expression must refer to exactly one column
Hej Pawel,
sprobuj tego,
M = VAR __tbl = FILTER( GROUPBY( CALCULATETABLE( WH_Invent_Trans, --KEEPFILTERS( WH_Invent_Trans[TransType] IN { 0, 9 } ), KEEPFILTERS( WH_Invent_Trans[QTY] <> BLANK() ), ALLEXCEPT( WH_Invent_Trans, WH_Invent_Trans[ItemID] ) ), WH_Invent_Trans[ItemID], "@maxDate", MAXX( CURRENTGROUP(), WH_Invent_Trans[DatePhysical] ) ), VAR __days = DATEDIFF( [@maxDate], TODAY() -1, DAY ) RETURN __days > 0 && __days <= 276 --changed as 15 was out of range ) RETURN CALCULATE( SUM( WH_Invent_Trans[Inventory Value EUR] ), TREATAS( __tbl, WH_Invent_Trans[ItemID], WH_Invent_Trans[DatePhysical] ) --DATESYTD(DimDates[Date]); )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.Chyba nie do końca dobrze wytłumaczyłem wszystko.
Chodzi o to, żeby do wiekowania, wybór indeksów był na podstawie trans type 0 i 9. -> czyli w _tbl filtr musi pozostać.
Natomiast do wyliczenia wartości wybranych indeksów były brane wszystkie transakcje (bez filtra na trans type). -> jak wyłączyć filtr na trans type w Treatas?
