Forum Discussion

perezco's avatar
perezco
Advocate III
7 years ago

using treatas to filter data

Order#Qty_itemstreatas_distinct_commoditytotal_orders_with_1_Commodity
666289448611
666610474111
666076242511
66682382465 
66682210454 
66667869376 
666322541108 
66673054732 
66569990122 
66684804022 
66621212654 
66403793642 

 

 

The above table is compose virtual connection of  fact dispatch item ordered with commodity part label by using treatas to allow me use te commodity_name

 

looking to get without showing the order#

Qty_itemstreatas_distinct_commoditytotal_orders_with_1_Commodity
611
111
511

12 (total                                                                                   3(total order)

qty items)   

 

------------------------------------------------------------

formulas that I been trying:

  1. MEASURES

    _Total Dispatches with 1 Commodity Qty2 =

    VAR T1 =

    SUMMARIZE(

    'Fact - DIO',

    -- Group by ---

    ''Fact - DIO'[Order_ID],

    -- Count distinct Commodity per Dispatch

    "DistCount", DISTINCTCOUNT ('Fact - DIO'[Commodity Description2] )

    )

    VAR ItemOrderedQty = SUM('Fact - DIO'[ITM_QTY])

     

    return

    sumx(

    filter(

    T1,

    [DistCount] = 1

    ),

    ItemOrderedQty

    );

    TreatAS_Distinct_Commodity =

    CALCULATE(

                DISTINCTCOUNT('Hier - Commodity'[COMDTY_NM])

                , TREATAS (

    CALCULATETABLE(VALUES ( 'Fact - DIO'[ITM_NBR] )),

    'Hier - Commodity'[PART_NBR]

    )

            );

    _Total Dispatches with 1 Commodity =

    COUNTROWS (

    FILTER (

    SUMMARIZE (

    'Fact - DIO',

    'Fact - DIO'[SVC_DSPCH_ID],

    -- Count distinct Commodity per Dispatch

    "DistCount", DISTINCTCOUNT ('Fact - DIO'[Commodity Description2] )

    ),

    [DistCount] = 1

    )

    )

  2. COLUMN   ////(column create in the 'Fact - DIO')

    Commodity Description2
    = CALCULATETABLE (

    VALUES ( 'Hier - Commodity'[COMDTY_NM] ),

    TREATAS (

    CALCULATETABLE ( VALUES ( 'Fact - DIO'[ITM_NBR] ) ),

    'Hier - Commodity'[PART_NBR]

    )

    )

 

Need Help