Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a database with three columns: id, date created and end date. I need to make a graph by month/year and the value is the count of ids that its date created is before or equal to that month/year of the axis and the end date is after or equal to it.
Example:
id | date created | end date
123 05/08/2019 12/10/2019
456 02/07/2019 18/12/2019
In aug/19 the count is 2.
In jul/19 the count is 1 (only 456)
In dec/19 the count is 1 (only 456)
I can't find a way to do this because the axis is not a column of the database, and the filters are set to a fixed date, it's not dynamic (so the end date is always set to after the date created).
Can someone help me with this?
Solved! Go to Solution.
@Anonymous
Hope you have a date dimension.
The method i know is to create a new column in Date dimension as below.
Column =
CALCULATE (
COUNT ( 'Table'[id] ),
FILTER (
'Table',
'Table'[date created] <= 'Table 2'[Date]
&& 'Table'[end date] >= 'Table 2'[Date]
)
)
Now use the month column in axis and (Max of New_column) as value in the graph.
If this helps, mark it as a solution
Kudos are nice too.
@Anonymous
Hope you have a date dimension.
The method i know is to create a new column in Date dimension as below.
Column =
CALCULATE (
COUNT ( 'Table'[id] ),
FILTER (
'Table',
'Table'[date created] <= 'Table 2'[Date]
&& 'Table'[end date] >= 'Table 2'[Date]
)
)
Now use the month column in axis and (Max of New_column) as value in the graph.
If this helps, mark it as a solution
Kudos are nice too.
That solved it, yes, thank you.
But if I want to have the ids that were filtered, how would I do it? I tried adding a new column but I can't get around the "the expression refers to multiple columns" error.
Add it to the filter condtition.
CALCULATE(COUNT('Table'[id]),FILTER('Table','Table'[date created]<='Table 2'[Date] && 'Table'[end date]>='Table 2'[Date] && 'Table'[id] = 123))
If it helps, mark it as a solution
Kudos are nice too
@Anonymous
Oh no that's not what I meant
I want to print all the ids that were successfully filtered in that other column
Ex: if the count is 4, I wanna print 123;456;789;147
Doesn't really matter the way it's printed, if it's all in the same column as in "123;456" or one id in every column, but I need the values
Thank you! That worked perfectly
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |