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 there,
I am trying to get the recent date value for the table below.
I have tried the max and lastdate function but I cant seem to get the results i am expecting.
I was able to get a seperate latest effective date column with Measure = Calculate(MAX(Table(Effective_Date), Allexcept(Table, table(id))
Table:
ID | Emp_ID | Interval | Effective_Date | Score |
1 | 1001 | 0 | 01/01/2020 | 3.1 |
2 | 1001 | 0 | 08/26/2020 | 3.3 |
3 | 1001 | 3 | 11/20/2020 | 3.5 |
4 | 1001 | 5 | 05/05/2020 | 3.2 |
5 | 1001 | 5 | 2/1/2021 | 4.1 |
6 | 1002 | 0 | 1/1/2022 | 2.0 |
Expected result if i want to see the recent date values if i filter by interval 0.
ID | Emp_ID | Effective_Date | Score |
2 | 1001 | 8/26/2020 | 3.3 |
6 | 1002 | 1/1/2022 | 2.0 |
or if i dont filter any of the intervals i should get all client IDs with a recent date value witht their corresponding intervals.
ID | Emp_ID | Interval | Effective_Date | Score |
2 | 1001 | 0 | 8/26/2020 | 3.3 |
6 | 1002 | 0 | 1/1/2022 | 2.0 |
3 | 1001 | 3 | 11/20/2020 | 3.5 |
5 | 1001 | 5 | 2/1/2021 | 4.1 |
Solved! Go to Solution.
Hello @yve214.
I was able to get this to work by creating a new table with the following DAX:
New table =
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[Emp_ID], 'Table'[Interval] ),
"Effective Date", CALCULATE( MAX( 'Table'[Effective_Date] ) ),
"Score", CALCULATE( MAX( 'Table'[Score] )),
"ID", CALCULATE( MAX( 'Table'[ID] ) )
)
I created a .pbix file that you can download here.
-Steve
Hi @yve214. Ah, I think understand now. See if this works for you:
Count of Emp IDs =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Emp_ID] ),
FILTER (
'Table',
VAR CurrentRowEmpID = 'Table'[Emp_ID]
RETURN
VAR IntervalZeroEffectiveDate =
CALCULATE (
MIN ( 'Table'[Effective_Date] ),
ALL ( 'Table' ),
'Table'[Interval] = 0
&& 'Table'[Emp_ID] = CurrentRowEmpID
)
RETURN
'Table'[Interval] = 6
&& 'Table'[Effective_Date] > IntervalZeroEffectiveDate
)
)
Hello @yve214.
I was able to get this to work by creating a new table with the following DAX:
New table =
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[Emp_ID], 'Table'[Interval] ),
"Effective Date", CALCULATE( MAX( 'Table'[Effective_Date] ) ),
"Score", CALCULATE( MAX( 'Table'[Score] )),
"ID", CALCULATE( MAX( 'Table'[ID] ) )
)
I created a .pbix file that you can download here.
-Steve
Please can i ask one more question? I am trying to "count the emp IDs where the interval is 6 making sure the dates at interval 6 is greater like (effective date at the 6 interval > the effective date at interval 0). Is that something you can help me with.
Here is how i approached it. I created two date measures for both interval 0 and interval 6. I did a if((date_at_interval_6) > (date_at_interval_0) && table[interval] = 6, distinctcount(table[emp_ID]). But i keep getting blank.
Hi @yve214.
Give this a try:
Count of Emp IDs =
COUNTROWS(
FILTER(
'Table',
VAR CurrentRowEmpID = 'Table'[Emp_ID]
RETURN
VAR IntervalZeroEffectiveDate =
CALCULATE(
MIN( 'Table'[Effective_Date] ),
ALL( 'Table' ),
'Table'[Interval] = 0 && 'Table'[Emp_ID] = CurrentRowEmpID
)
RETURN
'Table'[Interval] = 6
&& 'Table'[Effective_Date] > IntervalZeroEffectiveDate
)
)
Thank you again for all your help. I was able to get the same value I got but the dates didnt filter that count as expected.
I had another measure listed as such:
var _max0 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =0),Table[Effective_Date])
var _max5 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =5),Table[Effective_Date])
return
calculate(distinctcount(table[emp_ID]), filter(Table, Table[Interval] =5 && _max5>_max0))
which gave me the number but I get that only in a row context, not a card value. What can i change here?
Hello @yve214. You're welcome. Could you give me some more info about what you mean by "the dates didn't filter the account as expected"? Maybe even provide me a .pbix with sample data.
It seems to be working OK in the sample .pbix file that I created here. Perhaps you could take a look at my file also.
Sorry the date filter works for the interval 6 > interval 0.
I was able to add a row 13 for emp ID 2001 to test how it works and I noticed
When i placed the results in a tableview it gave me a context view butI see 2001 twice since it counted the rows rather than the distinct call out. I tried tweaking yours too but I get the same total as if the dates didnt filter.
sorry not sure why i cant attach file
Hi @yve214. Ah, I think understand now. See if this works for you:
Count of Emp IDs =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Emp_ID] ),
FILTER (
'Table',
VAR CurrentRowEmpID = 'Table'[Emp_ID]
RETURN
VAR IntervalZeroEffectiveDate =
CALCULATE (
MIN ( 'Table'[Effective_Date] ),
ALL ( 'Table' ),
'Table'[Interval] = 0
&& 'Table'[Emp_ID] = CurrentRowEmpID
)
RETURN
'Table'[Interval] = 6
&& 'Table'[Effective_Date] > IntervalZeroEffectiveDate
)
)
@SteveHailey Thank you so much, dont know why i didnt think of a summarize function. Worked like magic.
I am not sure if Power BI is the best choice for this task. The result can be easily achieved in excel with a slicer.
...
my Power BI Version, without a DAX Expresion
Thank you @emjp , I was able to get it from using a summarize (select statement) function.
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 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |