Forum Discussion

tkwh1's avatar
tkwh1
Frequent Visitor
2 years ago
Solved

show values in data table filtered under different conditions

Hello all,  I need help with DAX code that would produce results in a matrix table under the following conditions.  So far not much luck..   Conditions For example, i have the following data with...
DAX
filtered values
Matrix table
Table
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI tkwh1,

    It seems like I missed the first condition to filter items which 'Invoiced' field equal to 'No' , you can try to the following measure formula if it suitable for your requirement:

    Distinct Shipped Count Per Inventory =
VAR InventoryList =
    CALCULATETABLE (
        VALUES ( Table1[Inventory] ),
        FILTER ( ALLSELECTED ( Table1 ), [Invoiced] = "No" )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table1[Shipped] ) ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [Invoiced] = "Yes"
                && [Inventory] IN InventoryList
        ),
        VALUES ( Table1[Inventory] )
    )

    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI tkwh1,

You can try to use following measure formula to get Invoiced and shipped count based on inventory group:

Distinct Shipped Count Per Inventory =
CALCULATE (
    COUNTROWS ( VALUE ( Table1[Shipped] ) ),
    FILTER ( ALLSELECTED ( Table1 ), [Invoiced] = "Yes" ),
    VALUES ( Table1[Inventory] )
)

Regards,

Xiaoxin Sheng

tkwh1's avatar
tkwh1
Frequent Visitor
2 years ago

hi Xiaoxin,

Thank you for your response, really appreciate the suggested code you provided.

 

I tried it and thought it had worked initially, but after checking the result, it seems that the "Inventory" items displayed included other Inventory items as well.

Using your code I tried different variations but still cannot get the exact result I need.,,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI tkwh1,

    It seems like I missed the first condition to filter items which 'Invoiced' field equal to 'No' , you can try to the following measure formula if it suitable for your requirement:

    Distinct Shipped Count Per Inventory =
VAR InventoryList =
    CALCULATETABLE (
        VALUES ( Table1[Inventory] ),
        FILTER ( ALLSELECTED ( Table1 ), [Invoiced] = "No" )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table1[Shipped] ) ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [Invoiced] = "Yes"
                && [Inventory] IN InventoryList
        ),
        VALUES ( Table1[Inventory] )
    )

    Regards,
    Xiaoxin Sheng

    • tkwh1's avatar
      tkwh1
      Frequent Visitor
      2 years ago

      hi Xiaoxin,

      Thank you for your response and the additional code.

       

      I have tried this and it shows up as blank, so I will see what else I can do on my end.

      In the meantime I have accepted your response as the solution (please let me know if it is not the case).