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.
Hello all
I have a table of objects.
These objects are available within a data range.
I now need to set a qualifying date to show only the objects that are active on that qualifying date.
Until now it just uses "Today" in the formula. (dateActiveOn <= Today) & (dateDeadOn > Today)
Is there a way to let the user set something like a variable that i can use in the formula to filter the objects?
Solved! Go to Solution.
Hi @Anonymous
Explaination for the formula as below
qualifying1 =
IF (
[selected date] < MAX ( Sheet5[datedeadon] ) //"max" -> get the current row of a column,
&& [selected date] >= MAX ( Sheet5[dateactiveon] ),
1,
0
)
I make a new test as below
in my test, [DeadActiveOn] is date/time type, DateDeadOn and Date (Date table) are in date type.
Could you share a screenshot to let me know your data and what's error of the qualifying measure?
Best Regards
Maggie
Hi,
It is possible to do this with the Query Editor. The 2 date columns can be merged into single one. To get more specific help, share some data and show the expected result.
Hi @Anonymous
Create a date table which is not connected to your data table.
Date table = ADDCOLUMNS ( CALENDARAUTO (), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ), "day", DAY ( [Date] ), "weeknum", WEEKNUM ( [Date], 2 ), "weekday", WEEKDAY ( [Date], 2 ) )
scenario 1: select one date from the "date table"
create measures in your main data table
selected date = SELECTEDVALUE('Date table'[Date]) qualifying1 = IF ( [selected date] < MAX ( Sheet5[datedeadon] ) && [selected date] >= MAX ( Sheet5[dateactiveon] ), 1, 0 )
add "year","month","day" from the "date table" in the slicers,
add measure "qualifying" in Visual level filter.
scenario 2: select a date period from the "date table"
create measures in your main data table
min = MIN('Date table'[Date]) max = MAX('Date table'[Date])
qualifying2 = IF ( [min] >= MAX ( Sheet5[dateactiveon] ) && [max] < MAX ( Sheet5[datedeadon] ), 1, 0 )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-juanli-msft
With my inital matrix-table it showes 15k objects less than it should be. Because of that i thought it just dont work.
I made a new table with the ID, activeOn, deadOn and qualifying. When i count the qualifyings (true) i get the correct number.
So your solution works, i just need to implement it into the matrix correctly.
Hello @v-juanli-msft
Thanks for this answer.
Im looking to get scenario 1 to work.
Its about the same direction i tried yesterday night, but with a few changes...
For now, i dont get it to work.
Following Questions:
The qualifying measure is always false, cant get it to true
Hi @Anonymous
Explaination for the formula as below
qualifying1 =
IF (
[selected date] < MAX ( Sheet5[datedeadon] ) //"max" -> get the current row of a column,
&& [selected date] >= MAX ( Sheet5[dateactiveon] ),
1,
0
)
I make a new test as below
in my test, [DeadActiveOn] is date/time type, DateDeadOn and Date (Date table) are in date type.
Could you share a screenshot to let me know your data and what's error of the qualifying measure?
Best Regards
Maggie
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |