DAX Command- Need Help

To create the "No of Nights" column based on your described logic in Power BI using DAX:

Logic Summary:

1. TourRefNo: Group by TourRefNo.
2. SuppType: Only consider SuppType = "HTL".
3. BookingDate: Count the distinct BookingDate if there are two or more HTL entries for a TourRefNo.

The "No of Nights" value should then apply to all rows within the same TourRefNo.

DAX Solution:

1. Step 1: Create a calculated column for counting distinct booking dates of "HTL":

    

   HTL_BookingDatesCount = CALCULATE( DISTINCTCOUNT('Table'[BookingDate]), FILTER('Table', 'Table'[TourRefNo] = EARLIER('Table'[TourRefNo]) && 'Table'[SuppType] = "HTL" ) )

2. Step 2: Create the "No of Nights" column based on the count of HTL booking dates:

    

    

   No_of_Nights = VAR HTL_Count = CALCULATE( COUNTROWS('Table'), FILTER('Table', 'Table'[TourRefNo] = EARLIER('Table'[TourRefNo]) && 'Table'[SuppType] = "HTL" ) ) RETURN IF( HTL_Count >= 2, CALCULATE( DISTINCTCOUNT('Table'[BookingDate]), FILTER('Table', 'Table'[TourRefNo] = EARLIER('Table'[TourRefNo]) && 'Table'[SuppType] = "HTL" ) ), 0 )

Explanation:

1. HTL_BookingDatesCount: Counts distinct BookingDate values for rows where SuppType = "HTL".
2. No_of_Nights:
   • Checks if there are at least two SuppType = "HTL" rows for a TourRefNo.
   • If true, calculates the distinct count of BookingDate for HTL rows under the same TourRefNo.
   • Otherwise, assigns 0.

Outcome:

• All rows for the same TourRefNo will display the calculated "No of Nights" based on the logic.

Let me know if you'd like additional help!

Thanks for the reply from 123abc , please allow me to add some more information:
Hi  Anonymous ,

You can use the var() function to store the result of an expression as a named variable, which can then be passed as a parameter to other metric expressions, saving code length and memory consumption.

VAR keyword (DAX) - DAX | Microsoft Learn

1. Create calculated column.

Number of Nights =
var _count=
CALCULATE(
    DISTINCTCOUNT('Table'[BookingDate]),
    FILTER('Table',
    [TourRefNo]=EARLIER('Table'[TourRefNo])&&[SuppType]="HTL"))
return
IF(
    _count=BLANK(),0,_count)

Min 2 night =
 IF(
    [Number of Nights]>=2,"y","n")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly