Forum Discussion
Remove other columns
Hi kassymov_su ,
Very difficult to give an exact answer just by looking at your M code. I would really need to see the actual source data structure to be sure, but I think the following things are where you need to start:
1) You need a calendar dimension table. All of your ISODate_X calculations should be done in a separate table. If you build your calendar table in dataflows, for example, you only need to do it once and it will always be available to every report in future.
Reference:
https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/
2) You need to unpivot your fact table so that your dd.MM.yyyy column headers are in a single column. You can do this by multi-selecting any column that isn't a dd.MM.yyyy column, going to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.
Firstly, this makes it very easy to filter on this column in Power Query to only include your reporting period dates.
Secondly, it allows you to make a relationship to your calendar table as follows:
3) Relate your tables together: calendar[Date] ONE : MANY factTable[newDateColumn]. You can now use your calendar[ISODate_X] fields in visuals and it will correctly filter you fact table to the relevant data.
Reference:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
Hi kassymov_su ,
As before, the best practice way to do this would be to unpivot your date columns (Point 2 above) then you can just apply a dynamic Table.SelectRows filter to the date column in Power Query. This also structures your data in the most efficient format for reporting.
If you want the range to be selected by the end user, then you would use a proper related calendar table to populate a slicer.
I think we're at the wrong end of an XY Problem here to be honest so, if you're able to share your source data and exactly what you want to achieve, we can look at getting the best overall solution for your scenario, otherwise we'll probably just go round in circles.
Pete
I agree with BA_Pete - I believe my post directly answers your question while his post provides the best solution for the end product.