DAX code filtering tabled based on selected value (show other values)
Hi all, I have an issues to which I cannot find the solution. I hope someone can help me out. I've created an example dataset to explain the problem: customers order food and drinks in a restaurant. They can order multiple types of food and drinks during 1 visit. Here is a sample of 5 different customers with their orders: I can edit this table to make it a one record per customer list: Now what I would like to know is when I select one item in a slicer/filter, what are the other most ordered items per type (food/drinks) based the customers that ordered the selected item? Example: I select as item Fries. Customer B, C and E ordered Fries. During the same order, they also ordered other food and drink items. I want to count the number of times the other items were ordered to see which item is most often ordered together with fries. This is what I would like to see then: My thoughts were to first create new DIM table with the type and item combinations (a food and drinks list) which can then be used for the filter. I can create a relationship between the new DIM table and the FACT table in the model view so that I can filter the customers with orders that contain the selected item. Then I somehow need to use this list of selected customers to filter another table (a copy of the original FACT table with all the customers and their order information?) so that I can see the whole order of these customers. And then I can create a visual of the number of items, per type, in which I exclude the selected item. I hope someone can help me out 🙂 Many thanks, Anne
DAX Calculation - HELP
I have this summarized dataset grouped by multiple variables. This is a screenshot of a table I created from the dataset (other variables are being used as slicers), each index point has mulitple rows in the main dataset 2023 is defined as CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2023 }) 2022 is defined as CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2022 }) I wanted to create two seperate measures each for 2023 and 2022 that will take the previous row value and then add it at each step with the current value at that index point, it should look something like this
Rolling Median Time Period
Hi all, I need to dynamically calculate the median of the last three months of the starting time of the distinct jobs of the current selected date, that is: I want to calculate, for each distinct job key, the median of its "Start" time of the last three months. I tried: MedianStartingL3M = VAR NumberOfDays = 91 VAR MaxDay = SELECTEDVALUE('Fact Jobs'[job_start_datetime]) VAR MinDay = MaxDay-NumberOfDays VAR Result = CALCULATE(PERCENTILE.EXC('Fact Jobs'[job_start_time],0.5),ALL('Fact Jobs'),'Fact Jobs'[job_key]=SELECTEDVALUE('Fact Jobs'[job_key]),'Fact Jobs'[job_start_datetime]<=MaxDay,'Fact Jobs'[job_start_datetime]>MinDay) RETURN Result Where job_start_time = time when the job starts. In the first example: 14:28:49 job_key = unique identifier of the job job_start_datetime = datetime when the job starts. In the first example 15/12/2022 14:28:49 But it's not working Could someone help me out? Thanks in advance for your help. BR, Sara
Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.Data Snapshot for 60 days and 90 days
Hi, I am hoping for some help here to be able to get and create column for 60 days snapshot and 90 days snapshot using 2 dates column (received and completed). I have a dataset called system ticket. below are sample data. -I'm trying to convert the code from tsql to powerBI but could not figure what function to be use. Is there any other approach to get the snapshot? case when replace(substring(convert(varchar(50),dateadd(month, 1, received),111),1,7),'/','-') = replace(substring(convert(varchar(50),completed,111),1,7),'/','-') then 1.0 else 0 end FG_Snapshot_60, Thanks in advance.
