DAX Command- Need Help

[DanduMani104]

In above pic , there is some logic, But I unable to write proper DAX add column logic.
I want to add the column"No of Nights ", that takes the reference of Tour ref no, supp type and booking date. If tour ref has >=2 of count of supp type HTL, It takes dist.count of booking dates and indicates that number to all the supp type for Perticular Tour ref no.

How can I do it...Please help

[123abc · Accepted solution]

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!

[Anonymous · Accepted solution]

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

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

[123abc · Accepted solution]

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!