Forum Discussion
Create a table that only includes the first and last row from the report's filtered range
I am pretty new at PBI and DAX...
I feel like this should be simple, but I can't find a straightforward way to acomplish it.
when I create this table:
xMV = SELECTCOLUMNS(
extHoldings,
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
everything works as expected, and I see the filtered range based on the report date slicer.
I am trying to write a dax formula to create a table that only includes the first and last row from the filtered extHoldings table.
There are multiple values on the first and last day - it is ok if there are many rows in the table, as long as the only dates included are the first and last from the report's filtered range.
I tried this, but it is not working... This seems to inclue the max and min from the entire data table, not the report's filtered range. so visulizations include no data. (Ultimately, I would like to use a date slicer based on the dimDate[Date] column from my date table..):
xMVbe =
VAR MaxDate = CALCULATE ( MAX ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) )
VAR MinDate = CALCULATE ( MIN ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) )
VAR FilteredTable =
SELECTCOLUMNS (
CALCULATETABLE (
extHoldings,
extHoldings[Rep Date] = MaxDate || extHoldings[Rep Date] = MinDate
),
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
RETURN
FilteredTable
Any help would be GREATLY appreciated. I have been stuck on this for days!
1 Reply
- AnonymousNot applicable
Hi phishneslo ,
Please try:
xMVbe = VAR MaxDate = CALCULATE ( MAX ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) ) VAR MinDate = CALCULATE ( MIN ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) ) VAR FilteredTable = SELECTCOLUMNS ( CALCULATETABLE ( extHoldings, KEEPFILTERS ( extHoldings[Rep Date] = MaxDate || extHoldings[Rep Date] = MinDate ) ), "Port Code", extHoldings[Port Code], "Symbol", extHoldings[Symbol], "Date", extHoldings[Rep Date], "Flow", extHoldings[M Value] ) RETURN FilteredTableBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum