Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
)
Hi @perezco
I can't reproduce your problem.
Please show me more details like simple example data, relationships of tables.
In addition, please refer to
How to use the TREATAS function in DAX
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |