Forum Discussion

Baye's avatar
Baye
Frequent Visitor
7 years ago
Solved

Double condition - Stock table

Hi! I've a problem in DAX I can't solve. Perhaps you could help me... In an stock movement table, I have the next columns: Date-time, Product id, Movement id, Order id and Stock. The final stock...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Baye 

    Just try this formula:

    Column 2 = 

VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    or

    Column 3 = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  IF (
        Stock[Datetime] = MAXdt
            && Stock[Number] = MAXNumID,
        CALCULATE ( SUM ( Stock[Stock] ) )
    )

    Result:

     

    Best Regards,
    Lin

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Baye 

    Just try this measure

    Measure = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct],'Calendar'[Month],'Calendar'[Month Number])) 
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( ALLEXCEPT(Stock,Stock[IdProduct]), Stock[Datetime] = MAXdt )
    )

return
CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    Best Regards,

    Lin

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Baye 

Just try this formula:

Column 2 = 

VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

or

Column 3 = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  IF (
        Stock[Datetime] = MAXdt
            && Stock[Number] = MAXNumID,
        CALCULATE ( SUM ( Stock[Stock] ) )
    )

Result:

 

Best Regards,
Lin

Baye's avatar
Baye
Frequent Visitor
7 years ago

Hi! I'm back again...

 

I've copied your two options into two columns in my model and after I've created a measure for each one as MAX(Column2) (as the formula returns a value for each row), and SUM(Column3) (as the formula returns a value just for the last value).

 

I've noticed that your columns return the last value in the stock, but if I try to filter the values by month, your Column 2 returns the max of total stock (not filtered by month), and your Column 3 returns "blank" if the last value in stock is in another month from the filtered one.

 

Any of them return the correct stock for the last date/number in the selected filter.

 

Any idea of how to solve this issue?

 

Thanx!!!

 

B.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Baye 

    First, you should know that calculated column and calculate table can't be affected by any slicer. 
    Notice:
    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
    here is reference:
    https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

     

    Second, you could use “all” functions to add a measure

    https://docs.microsoft.com/en-us/dax/allexcept-function-dax

     

    Measure = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct])) 
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( ALLEXCEPT(Stock,Stock[IdProduct]), Stock[Datetime] = MAXdt )
    )

return
 CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    By the way: add month column into red part above.

     

    Best Regards,

    Lin

    • Baye's avatar
      Baye
      Frequent Visitor
      7 years ago

      Ok, I've created a measure instead a calculated column as you propose.

       

      Measure = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct],Stock[Date])) 
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( ALLEXCEPT(Stock,Stock[IdProduct],Stock[Date]), Stock[Datetime] = MAXdt )
    )

return
 CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

      The result measure is valid in PowerPivot, but now the problem is that when I insert the measure into the pivot table, it returns blank for each product.

       

      I'm really lost, is the first time I can't solve a problem...

       

      Really appreciate your help,

       

      B.

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Baye 

        if I try to filter the values by month

        What field do you use to filter the value by month, isn't month field? and could you share a simple sample pbix file and expected output? that would help tremendously.

        Do mask sensitive data before uploading.

        Best Regards,
        Lin