How structurized and make order with DAX statements - best practices
Hello Guys, How to make a structure to find easily all measures? How do you organize it? Maybe you are putting them into folder with page name or you have seperated measure table? How to not look for them in whole model while analyzing data? Best, Jacek
DAX Calculated Column Sequence of Event Actions and Sessions
I have dataset with users actions logs. We have next data collection logic - every user have it own user_id and we record log of event actions during user session. user_id session_id dateTime event 1 aa 2023-01-01 13:12:11 login 1 aa 2023-01-01 14:12:10 buy 1 bb 2023-01-02 11:12:10 page 2 cc 2023-01-01 10:11:01 login 2 gg 2023-01-03 11:12:11 logout 2 gg 2023-01-03 13:11:03 click 2 gg 2023-01-03 14:10:07 logout The main goal is prepare output and add 2 calculated columns: 1. with events action sequence during every user session and 2. with session sequence during all user lifetime by SQL. Expected output: user_id session_id dateTime event event_seq session_seq 1 aa 2023-01-01 13:12:11 login 1 1 1 aa 2023-01-01 14:12:10 buy 2 1 1 bb 2023-01-02 11:12:10 page 1 2 2 cc 2023-01-01 10:11:01 login 1 1 2 gg 2023-01-03 11:12:11 logout 1 2 2 gg 2023-01-03 13:11:03 click 2 2 2 gg 2023-01-03 14:10:07 logout 3 2 What was done from my side: 1. Successfully create column with events action sequence during session Event Action Sequence = COUNTROWS ( FILTER ( CALCULATETABLE ( Sheet1, ALLEXCEPT (Sheet1, Sheet1[session_id]) ), Sheet1[Date] < EARLIER ( Sheet1[Date] ) || ( Sheet1[Date] = EARLIER ( Sheet1[Date] ) && Sheet1[Time Action] <= EARLIER ( Sheet1[Time Action] ) ) ) ) 2. Sessions Sequence Order By each user - calculated column not prepared - I stucked on it. Only prepared time for each session when it started (minimum time for each session) Min Session DateTime = CALCULATE( MIN(Sheet1[dateTime]), ALLEXCEPT(Sheet1,Sheet1[session_id]) ) So the main help request, how to solve this and create one more column with sessions sequene number orderd ascending by timestamp for each user. Sharing link to my pbix file https://drive.google.com/file/d/1807l9E07oNJv9l5rKWXKxDoc6rniyiYG/view?usp=sharing And sharing link to my sample: https://docs.google.com/spreadsheets/d/1bb7VId8lJ-NQkF43YS739kfc5Ur-1cGy/edit?usp=sharing&ouid=103990807236416334574&rtpof=true&sd=true
Sort matrix by measure..but 2nd level rows ignore sort
Hey everyone. I have a simple matrix that sorts by volume. I also have a time calculation group for Current and PY and the ordinal is set to the correct sort order. However...sorting the Matrix overrides the sort order of the calculation group. Is there a way to sort the matrix by the volume measure...but ignore the sort for the calculation group?Search In Different Order
I'm looking to have a search field that looks in a table where it searches the same words, but the words could be in a different order. In excel, it would be similar to the contains and contains filter instead of the exact filter. Example Description Field: Pen Blue Medium Bic In the search field if "blue pen" is entered I would like for this description to come up. It's only coming up if I type the words in the exact same order. Eg. "Pen blue", "blue medium" & "medium bic" Thanks in advance for your help! 🙂 Laurinda
Sort Date and Value columns
Hello, I have a data that looks like below: I would like sort the DEP, DEC and DOM column in descendig order for whatever the date range EST (Datetime )Column is selected. Example if I select date 1/1/2020 and 1/2/2020 for both the dates it should arange the DEP DEC and DOM values form Highest to lowest. 1/1/2020 high to low and 2/2/2020 high to low, 1/3/2020 high to low. In current scenario If i try to order them in descending order it mixes the dates and hours by decending the values considering the full range of all dates. I would like to see High to low for each date selection. Please suggest me the DAX or any solution based on the column names I have. Thank you.
RANKX AND ORDER
Hello, I have a question I'd like to raise so that I can resolve it. As you can see in the table I would like to add a column (RANKX ORDER LY) where appears the ranking of last year's products that had the same order as this year's products. The RANKX and RANKX LY columns work perfectly. YEAR PRODUCT QTY RANKX 2018 PEPSI 50 1 2018 COCA COLA 45 2 2018 ORANGE 30 3 2018 PEPSI MAX 25 4 2018 COCA COLA ZERO 10 5 RANKX RANKX LY RANKX ORDER LY 2019 COCA COLA 55 1 2 PEPSI 2019 PEPSI MAX 40 2 4 COCA COLA 2019 PEPSI 35 3 1 ORANGE 2019 COCA COLA ZERO 30 4 5 PEPSI MAX 2019 ORANGE 28 5 3 COCA COLA ZERO Thanks
