Forum Discussion
Helper IINeed a DAX where will be bulk_production count one value when production dyelot no same value
https://drive.google.com/file/d/1bApfixcHSuPBGXH8ieJo5QU88CIjiTrj/view?usp=sharing
Duplicate dyelot no marked with a red color, if dyelot no rows have duplicate no then bulk_production count one value and other dyelot no's bulk_production value will be 0. Like as Dyelot no 253126042 is double. Here are two bulk_productio value one is 312 & another is 213. So, one will be 312 and another will be 0. Thus, all will be same when you get duplicate dyelot no.
My actual data connected with SQL server. And summation of bulk_production value is more from my actual value. Bcoz there are many duplicate dyelot no.
Hi Emranit ,
Thank you for reaching out to the Microsoft Fabric community.
- Load your data into Power BI Desktop. We will now create a calculated column to adjust the Bulk Production values as per the required logic.
- Go to the Modelling tab and click on New Column. Then, enter the following DAX formula for the column:
DAX:
Bulk_Production_Adjusted =
VAR CurrentDyelot = 'ProductionData'[Production Dyelot]
VAR CurrentEndTime = 'ProductionData'[EndTime]
VAR FirstOccurrence =
CALCULATE (
MIN('ProductionData'[EndTime]),
FILTER (
'ProductionData',
'ProductionData'[Production Dyelot] = CurrentDyelot
)
)
RETURN
IF (
CurrentEndTime = FirstOccurrence,
'ProductionData'[Bulk_Production],
0
)
- In the Data View, examine the Bulk_Production_Adjusted column. For duplicate Production Dyelot entries, make sure only the row with the earliest EndTime retains the Bulk_Production value, while the rest should show 0.
- In the table visual, check the Bulk_Production_Adjusted column. For Dyelot values, the row with the earliest EndTime keeps the Bulk_Production value, while the other rows display 0.
Output:
If my answer addressed your query, kindly mark it as the Accepted Solution to assist others.
I'd also be grateful for a 'Kudos' if you found my response useful!
1 Reply
V-yubandi-msftCommunity Support
Hi Emranit ,
Thank you for reaching out to the Microsoft Fabric community.
- Load your data into Power BI Desktop. We will now create a calculated column to adjust the Bulk Production values as per the required logic.
- Go to the Modelling tab and click on New Column. Then, enter the following DAX formula for the column:
DAX:
Bulk_Production_Adjusted =
VAR CurrentDyelot = 'ProductionData'[Production Dyelot]
VAR CurrentEndTime = 'ProductionData'[EndTime]
VAR FirstOccurrence =
CALCULATE (
MIN('ProductionData'[EndTime]),
FILTER (
'ProductionData',
'ProductionData'[Production Dyelot] = CurrentDyelot
)
)
RETURN
IF (
CurrentEndTime = FirstOccurrence,
'ProductionData'[Bulk_Production],
0
)
- In the Data View, examine the Bulk_Production_Adjusted column. For duplicate Production Dyelot entries, make sure only the row with the earliest EndTime retains the Bulk_Production value, while the rest should show 0.
- In the table visual, check the Bulk_Production_Adjusted column. For Dyelot values, the row with the earliest EndTime keeps the Bulk_Production value, while the other rows display 0.
Output:
If my answer addressed your query, kindly mark it as the Accepted Solution to assist others.
I'd also be grateful for a 'Kudos' if you found my response useful!