Forum Discussion
DAX
I have used this dax to avoid duplicate in nad date,but if nad-date is repeating then PreviousDateB is coming same for both row where nad date is same,i need to take only one row from this two row where nad date is same but voyage type is different
Screen shot is added,where for first two row consider is coming where is it should come like one consider and one no
Hi nix - you can modify your DAX formula to incorporate additional logic. This logic prioritizes rows based on a specific criterion (like Voyage Type) to avoid duplicates.
Nad Date Between Location Timing =
VAR CurrentDateA = 'Voyage Type Calculation'[NAD_Date]
VAR CurrentDateB = 'Voyage Type Calculation'[NAD Date After UTC]
VAR VesselID = 'Voyage Type Calculation'[Vessel_ID]-- Calculate the Previous NAD Date After UTC for the same Vessel ID
VAR PreviousDateB =
CALCULATE(
MIN('Voyage Type Calculation'[NAD Date After UTC]),
FILTER(
'Voyage Type Calculation',
'Voyage Type Calculation'[Vessel_ID] = VesselID &&
'Voyage Type Calculation'[NAD Date After UTC] > CurrentDateB
)
)-- Find the Minimum Row ID or a Distinct Identifier for Prioritization
VAR MinRowForSameNADDate =
CALCULATE(
MIN('Voyage Type Calculation'[Row_ID]), -- Replace 'Row_ID' with a unique column
FILTER(
'Voyage Type Calculation',
'Voyage Type Calculation'[Vessel_ID] = VesselID &&
'Voyage Type Calculation'[NAD_Date] = CurrentDateA
)
)-- Determine the Result
VAR ResultVoyageType =
IF(
CurrentDateA > CurrentDateB &&
CurrentDateA < PreviousDateB &&
'Voyage Type Calculation'[Row_ID] = MinRowForSameNADDate, -- Ensure only one row gets "Consider"
"Consider",
"No"
)RETURN
ResultVoyageTypecheck the above dax logic and let know. if still issue exist, please share some sample data too.
2 Replies
Hi nix - you can modify your DAX formula to incorporate additional logic. This logic prioritizes rows based on a specific criterion (like Voyage Type) to avoid duplicates.
Nad Date Between Location Timing =
VAR CurrentDateA = 'Voyage Type Calculation'[NAD_Date]
VAR CurrentDateB = 'Voyage Type Calculation'[NAD Date After UTC]
VAR VesselID = 'Voyage Type Calculation'[Vessel_ID]-- Calculate the Previous NAD Date After UTC for the same Vessel ID
VAR PreviousDateB =
CALCULATE(
MIN('Voyage Type Calculation'[NAD Date After UTC]),
FILTER(
'Voyage Type Calculation',
'Voyage Type Calculation'[Vessel_ID] = VesselID &&
'Voyage Type Calculation'[NAD Date After UTC] > CurrentDateB
)
)-- Find the Minimum Row ID or a Distinct Identifier for Prioritization
VAR MinRowForSameNADDate =
CALCULATE(
MIN('Voyage Type Calculation'[Row_ID]), -- Replace 'Row_ID' with a unique column
FILTER(
'Voyage Type Calculation',
'Voyage Type Calculation'[Vessel_ID] = VesselID &&
'Voyage Type Calculation'[NAD_Date] = CurrentDateA
)
)-- Determine the Result
VAR ResultVoyageType =
IF(
CurrentDateA > CurrentDateB &&
CurrentDateA < PreviousDateB &&
'Voyage Type Calculation'[Row_ID] = MinRowForSameNADDate, -- Ensure only one row gets "Consider"
"Consider",
"No"
)RETURN
ResultVoyageTypecheck the above dax logic and let know. if still issue exist, please share some sample data too.
Hi nix
Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
