Forum Discussion

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago
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...
all
DATEDIFF
Filter
MAXX
Variable calculation
  • Mariusz's avatar
    Mariusz
    6 years ago

    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.

     

     

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago

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

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago

Mariusz 
Niestety to nie pomoglo, nadal wartości są z kosmosu.

Czy możesz mi wytłumaczyć co w formule zmieniło dodanie ALL do ostatniego CALCULATE?
Moze w ten sposob bede w stanie zidentyfikowac problem.

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi pawelj795 

     

    ALL( dimDate ) usunelow filtry z dimDate, to umozliwilo ( Running Total ) jak z DATESYTD tylko od pierwszej date w tabeli.

     

    Best Regards,
    Mariusz

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

    Please feel free to connect with me.
    LinkedIn

     

    • pawelj795's avatar
      pawelj795
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Mariusz 
      A czy przypadkiem ALL nie wyczyscilo tez innych filtrow powiazanych z data?
      Gdyz dodanie tej ostatniej czesci z poprzedniego posta nie spowodowalo zadnych zmian.

      Nie ma innej mozliwosci zamiany DATESYTD?

      • pawelj795's avatar
        pawelj795
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        Mariusz 
        Spróbowałem rozwiązania z innego posta odnośnie liczenia narastająco dla większej ilości niż rok.
        Chciałem połączyć ją z twoją, ale nie do końca to zadziałało.
        Może znajdziesz błąd.

        1. Na początku stworzyłem nową tabele dat, bez żadnych relacji do innych tabel.

        2. Następnie miara

        Spoiler
        VAR LastYearFirstDate =
        IF(
        SELECTEDVALUE('Calendar'[Year]) = BLANK();
        DATE( YEAR(LASTDATE(WH_Invent_Trans[Date Physical] ) ) -1;1;1);
        DATE(YEAR(
        CALCULATE(
        LASTDATE( WH_Invent_Trans[Date Physical] );
        FILTER(DimDates; DimDates[Year] = SELECTEDVALUE('Calendar'[Year]) )
        )
        ) -1; 1; 1)
        )

        VAR CurrentDate =
        IF(
        SELECTEDVALUE('Calendar'[Year]) = BLANK();
        MAX(DimDates[Date]);
        CALCULATE(
        MAX(DimDates[Date]);
        FILTER(DimDates; DimDates[Year] = SELECTEDVALUE('Calendar'[Year]) )
        )
        )

        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[Date Physical] )
        );
        VAR _days = DATEDIFF( [@maxDate]; TODAY() -1; DAY)
        RETURN _days >= 0 && _days <=15
        )
        VAR _date = MAX(DimDates[Date])

        RETURN
        CALCULATE(
        SUM(WH_Invent_Trans[Inventory Value EUR]);
        TREATAS(
        SUMMARIZE(_tbl;WH_Invent_Trans[ItemID]);WH_Invent_Trans[ItemID]);
        FILTER(
        ALLSELECTED(DimDates);
        DimDates[Date] >= LastYearFirstDate
        && DimDates[Date] <= CurrentDate
        )
        )