Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I am trying to create a DAX formula wherein I need to apply a filter and get total for specified columns in the raw data.
Here is what I am trying to accomplish:
Filter for Data A in Date 1 + Filter for Data B in Date 1 + Filter for Data A in Date 2 / (Total Data for Date 1 + Total Data for Date 2)
Wherein Date 1 is the reference date. Date 2 is the previous date. This needs to be done for all the dates in the “Req Date” column A.
Here is an example:
Reference date is “3/1/2017” in column A (Req Date)
(1)
Filter for column A (Req Date) for reference date of “3/1/2017”.
Then filter column I (Age) to get value = “1”.
Get the total count of loads in column J (Count of Load).
(2)
Filter for column A (Req Date) for reference date of “3/1/2017”.
Then filter column I (Age) to get value = “0”.
Get the total count of loads in column J (Count of Load).
(3)
Filter for column A (Req Date) for previous date of “2/28/2017”.
Then filter column I (Age) to get value = “0”.
Get the total count of loads in column J (Count of Load).
(4)
Filter for column A (Req Date) for reference date of “3/1/2017”.
Get the total count of loads in column J (Count of Load).
(5)
Filter for column A (Req Date) for previous date of “2/28/2017”.
Get the total count of loads in column J (Count of Load).
(6)
After getting all the results, the formula will look like this:
= (Result of Step 1 + Result of Step 2 + Result of Step 3) / (Result of Step 4 + Result of Step 5)
Below is the link of the raw data file for your reference. Please note that I am using Direct Query mode for the data.
https://www.dropbox.com/s/3snlyjsuxwwyct5/Sample%20file.xlsx?dl=0&m=
Appreciate your kind help.
Best regards,
Mark
Solved! Go to Solution.
Refer to the measure below.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN DIVIDE ( SUMX ( FILTER ( Table1, ( Table1[Req Date] = d && Table1[Age] IN { 0, 1 } ) || ( Table1[Req Date] = d - 1 && Table1[Age] = 0 ) ), Table1[Count of Load] ), SUMX ( FILTER ( Table1, Table1[Req Date] IN { d, d - 1 } ), Table1[Count of Load] ) )
You may start from the SELECTEDVALUE Function.
https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/
Thanks for the link but I am a beginner at writing DAX codes. Will you be able to help me in writing the code as per my specifications above? Appreciate your kind assistance.
Refer to the measure below.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN DIVIDE ( SUMX ( FILTER ( Table1, ( Table1[Req Date] = d && Table1[Age] IN { 0, 1 } ) || ( Table1[Req Date] = d - 1 && Table1[Age] = 0 ) ), Table1[Count of Load] ), SUMX ( FILTER ( Table1, Table1[Req Date] IN { d, d - 1 } ), Table1[Count of Load] ) )
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |