| Order# | Qty_items | treatas_distinct_commodity | total_orders_with_1_Commodity |
| 666289448 | 6 | 1 | 1 |
| 666610474 | 1 | 1 | 1 |
| 666076242 | 5 | 1 | 1 |
| 666823824 | 6 | 5 | |
| 666822104 | 5 | 4 | |
| 666678693 | 7 | 6 | |
| 666322541 | 10 | 8 | |
| 666730547 | 3 | 2 | |
| 665699901 | 2 | 2 | |
| 666848040 | 2 | 2 | |
| 666212126 | 5 | 4 | |
| 664037936 | 4 | 2 | |
| 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_items | treatas_distinct_commodity | total_orders_with_1_Commodity |
| 6 | 1 | 1 |
| 1 | 1 | 1 |
| 5 | 1 | 1 |
12 (total 3(total order)
qty items)
------------------------------------------------------------
formulas that I been trying:
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
)
)
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]
)
)
