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 All
I am still relatively new in my DAX journey and looking for some assitance. I have a snippet of a table below and I have tried to create a calculated measure that
1. sum of the count for latest date in table so below this would be 2 (6th Dec)
2. sum of the count for second date in table so below this would be 4 (3rd Dec)
I managed to get the required figure for point 1 but want this to be independent of other variables so always remains constant
Thanks in advance!
Solved! Go to Solution.
This will ignore any filters from anywhere
Prev CMI Count =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
Result
Try
Prev CMI Count =
VAR MaxDate =
MAX ( 'Table'[Date] )
VAR PrevDate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate )
VAR Result =
CALCULATE ( [Count total], REMOVEFILTERS ( 'Table' ), 'Table'[Date] = PrevDate )
RETURN
Result
Thanks @johnt75
Have tried this to get the max and the second max and have obtained the correct counts but these change if I have any slicers on my dashboard. Can the DAX formula be modified so the result is constant?
This will ignore any filters from anywhere
Prev CMI Count =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
Result
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |