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.
I have a table called asset with ids and last report time of each id. I want to get the ids which were reported in the last 24hrs, last 7 days, last 8-30 days and not reported for more than 30 days. Also, I want to get the unique ids since some ids are repeated.
Now, I have made a measure for each but in the dashboard when I am clicking on the measure visual, it has no effect on the other visuals present in the dashboard. How to go about it? Should I make a calculated column. If so what is the formula for the same? I have used formula of :
My dax measures:
Last_24_hr =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time] >= now() - 1 && assets[Last Report Time].[Date] <= now())
)
Last_7_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] >= TODAY() - 7 && assets[Last Report Time].[Date] <= TODAY() - 1)
)
8_to_30_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] >= TODAY() - 30 && assets[Last Report Time].[Date] <= TODAY() - 😎
)
Morethan_30_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] <= TODAY() - 30)
)
Solved! Go to Solution.
HI @John2,
So you mean you want to use one calculate field to get dynamic last n period based on current system date? If the is the case, I'd like to suggest you write multiple conditions in variable and add matched conditions flags to return the period levels.
For example:
Over one month level 1, last month level 2, last week level 3, last 3 days level 4, yesterday level 5. (the high level also matches the period tag that less than it)
You can use 'less than or equal to' mode to filter on level to display all match records. Also, you can add a new calculated column to show the description of these levels.
Regards,
Xiaoxin Sheng
HI @John2,
So you mean you want to use one calculate field to get dynamic last n period based on current system date? If the is the case, I'd like to suggest you write multiple conditions in variable and add matched conditions flags to return the period levels.
For example:
Over one month level 1, last month level 2, last week level 3, last 3 days level 4, yesterday level 5. (the high level also matches the period tag that less than it)
You can use 'less than or equal to' mode to filter on level to display all match records. Also, you can add a new calculated column to show the description of these levels.
Regards,
Xiaoxin Sheng
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 |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
82 | |
72 |