Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |