Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm trying to get the last values of a table, within the selected month.
* I have a slicer linked to a "Calendar" table with a column "DateOnly" (type Date). It allows the selection of an entire month (ex : May 2021)
* My table is defined like this :
DateOnly | Severity | Status | NumberOfAlerts | Source |
01/06/2021 | Low | InProgress | 3 | MDO |
01/06/2021 | Medium | Resolved | 9 | MCAS |
31/05/2021 | Informational | New | 7 | MDO |
31/05/2021 | Medium | New | 10 | MDO |
28/05/2021 | Medium | Resolved | 2 | MCAS |
* I have two other slicers, linked to a dimension table issued from the two "Severity" and "Status" columns (e.g. it allows filtering on Severity={High, Medium, Informational} and Status={InProgress, Resolved, New}).
* I want to set 3 visuals on which the Severity and Status filters may apply thanks to the slicers :
** one displays the total number of alerts
** one displays the total number of alerts for the MCAS source
** one displays the total number of alerts for the MDO source
I want to display these results for the last available date in the table, and when I say "last available date", it means "ignoring the other filters to define what the last date is" : e.g. in the example above, if my Severity and Stats slicers are set on "Medium" and "Resolved" for May 2021, I want to display "No data" because the last available data in the table is 31/05/2021 but there is no data for the "Medium" and "Resolved" combination.
I plan to define a measure that gives the result for the first result, and to use it on the three visuals : on the last two visuals, I will add a "visual filter" to filter on the source. 1 measure, 3 different visuals thanks to "visual filters".
Problem : I can find the DAX syntax to select the last available date.
For now, if I try this :
FilteredAlerts =
VAR MaxDate = MAX(M365Alerts[DateOnly])
RETURN
IF(MaxDate=BLANK(),
"No data",
SUMX(
FILTER(M365Alerts, M365Alerts[DateOnly]=MaxDate),
[NumberOfAlerts]
)
)
It works only when my "Severity" and "Status" slicers don't filter anything.
But when I select the "Medium" and "Resolved" combination (the Date slicer selects "May 2021"), e.g. it selects the intersection of "Severity", "Status" and "Date", e.g. the results for 28/05/2021.
What I want is a "No data" result for this combination (because the last available date is 31/05/2021, and not 28/05/2021).
I tried things to define the MaxDate with ALLEXCEPT, or REMOVEFILTERS or... I also tried to filter or to remove filters in the SUMX parameters... But I don't get the result I want.
Please, help ?
Thanks.
Solved! Go to Solution.
Hello,
For the records, here is the solution to select the latest available value :
_LatestAlerts =
VAR MaxDate_FiltersApplied = MAX('M365Alerts'[DateOnly])
VAR MaxDate_AvailableInTable = CALCULATE(
MAX('M365Alerts'[DateOnly]),
FILTER(
ALL(M365Alerts),
AND(MONTH('M365Alerts'[DateOnly]) = MONTH(SELECTEDVALUE('Calendar'[YearAndMonth])), YEAR('M365Alerts'[DateOnly]) = YEAR(SELECTEDVALUE('Calendar'[YearAndMonth])))
)
)
RETURN IF(
MaxDate_AvailableInTable<>MaxDate_FiltersApplied,
"No data",
CALCULATE(
SUM(M365Alerts[TotalNumberOfActiveUsers]),
FILTER(M365Alerts, M365Alerts[DateOnly]=MaxDate_AvailableInTable)
)
)
For the graph displaying 14 average values, despite the Calendar slicer, here is how I managed : using bookmarks and two buttons "Yearly" and "Monthly" :
Have fun (or not if, like me, you spent too much time to find a solution... 😄 ) !!!
Regards.
Hello,
For the records, here is the solution to select the latest available value :
_LatestAlerts =
VAR MaxDate_FiltersApplied = MAX('M365Alerts'[DateOnly])
VAR MaxDate_AvailableInTable = CALCULATE(
MAX('M365Alerts'[DateOnly]),
FILTER(
ALL(M365Alerts),
AND(MONTH('M365Alerts'[DateOnly]) = MONTH(SELECTEDVALUE('Calendar'[YearAndMonth])), YEAR('M365Alerts'[DateOnly]) = YEAR(SELECTEDVALUE('Calendar'[YearAndMonth])))
)
)
RETURN IF(
MaxDate_AvailableInTable<>MaxDate_FiltersApplied,
"No data",
CALCULATE(
SUM(M365Alerts[TotalNumberOfActiveUsers]),
FILTER(M365Alerts, M365Alerts[DateOnly]=MaxDate_AvailableInTable)
)
)
For the graph displaying 14 average values, despite the Calendar slicer, here is how I managed : using bookmarks and two buttons "Yearly" and "Monthly" :
Have fun (or not if, like me, you spent too much time to find a solution... 😄 ) !!!
Regards.
Hello,
Also, another question linked to the first one : on a page with a graph displaying all values for the current month, I also want to display another graph displaying the average number of data by month for the last 14 months. In other words : a graph with 14 points which are the average number of data for each months.
Problem : the slicer for the selection of the month for the first graph also applies on the second graph. Then, I guess I have to define a measure that calculates the average value for each month, ignoring the current slicer.
How do I ignore this filter ?
Thanks !
VAR _MaxDate=CALCULATE(MAX(M365Alerts[DateOnly]),ALL(M365Alerts)) VAR _ttl=CALCULATE([NumberOfAlerts],M365Alerts[DateOnly]=_MaxDate) RETURN IF(_ttl,_ttl,"No Data")
Hello wdx223_Daniel,
Thanks for your answer but it doesn't work :
* the formula seems to have a syntax error I corrected this way :
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |