cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pawelj795 Helper V
Helper V

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 🙂
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Quickly DAX measure fix - Filter

hej @pawelj795 

 

W pliku ktory wyslales nie bylo tego obiektu wiec musiale go wylachys z formuly.

 

Zalaczylem plik z formua.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

View solution in original post

31 REPLIES 31
Super User IV
Super User IV

Re: Quickly DAX measure fix - Filter

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.

 

pawelj795 Helper V
Helper V

Re: Quickly DAX measure fix - Filter

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

Super User IV
Super User IV

Re: Quickly DAX measure fix - Filter

Hi @pawelj795 

You can not use variable or && as a CALCULATE filter argument.

 

Can you create a small data sample and explain the result? seeing data and expected result always helps.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

pawelj795 Helper V
Helper V

Re: Quickly DAX measure fix - Filter

@Mariusz 
I must combine results from first measure you fixed as filter to my YTD calculation.

In meantime, I prepare some sample data.

Do you mind write in Polish? 
It would be much easier to explain 😉

pawelj795 Helper V
Helper V

Re: Quickly DAX measure fix - Filter

Super User IV
Super User IV

Re: Quickly DAX measure fix - Filter

Hi @pawelj795 

 

Sure we can try in Polish

 

Thanks 

Mariusz

pawelj795 Helper V
Helper V

Re: Quickly DAX measure fix - Filter

@Mariusz 

Tłumacząc najprościej, chciałbym policzyć wiekowanie stanów magazynowych na wczoraj dla każdego przedmiotu (itemu) na podstawie daty przyjęcia.
Bardzo ważną kwestią jest to, że dla każdego przedziału wiekowania musi być oddzielna miara z przedziałem, tzn:
1. Miara (0,15 dni

2. Miara (16,30 dni itd ...


Z tym, że do policzonego stanu magazynowego musi być brany wyłącznie określony typ transakcji (transtype 0,9).
Kolejny warunek to taki, że nie mogą być brane transakcje, których ilośc jest pusta (QTY<>blank).

Super User IV
Super User IV

Re: Quickly DAX measure fix - Filter

Hej @pawelj795  // Pawel

 

zalaczylem plik z kalkulacja "M" zobacz czy to jest o co ci chdzi.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

pawelj795 Helper V
Helper V

Re: Quickly DAX measure fix - Filter

@Mariusz 

 

Wydaje mi, że to może byc coś takiego 😉
Przetestuje na realnych danych i dam znac co wyszlo

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors