topic Re: DAX Command- Need Help in DAX Commands and Tips

DAX Command- Need Help

Anonymous — Tue, 10 Dec 2024 06:35:36 GMT

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

Re: DAX Command- Need Help

123abc — Tue, 10 Dec 2024 07:31:36 GMT

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

Logic Summary:

TourRefNo: Group by TourRefNo.
SuppType: Only consider SuppType = "HTL".
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:

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" ) )
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:

HTL_BookingDatesCount: Counts distinct BookingDate values for rows where SuppType = "HTL".
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!

Re: DAX Command- Need Help

Anonymous — Wed, 11 Dec 2024 06:00:06 GMT

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