Forum Discussion

Frequent Visitor

1 year ago

Solved

Change Table Columns Based on Slicer Selection

I have two table
1. Fact Main

2. Dim Calendar for Slicer

If slected value is Jan-25 ,
show Val_1 to Val_31 columns in table/matrix visual
If slected value is Feb-25 ,
show Val_1 to Val_29 columns in table/matrix visual
If slected value is June-25 ,
show Val_1 to Val_30 columns in table/matrix visual

Basically what I need to change Table(visual) columns based on Slicer Selection.
(display of columns depends on the number of days of selected month from the slicer.)
Thank you.

Need Help

speedramps
1 year ago
Hi again sharmil_28

Are you saying your fact table looks like this with 31 "val" columns

If yes then selcet the start of month column in tyeh trasnform menu Unpivot the other columns

Relace the "val" text with nothing and then Change the column data type to a whole numbner

Add a customer colum to add the start of month and "val" number to get the date

Date.AddDays([Start of month],[Attribute])

Learn more about Power Query here

https://www.youtube.com/watch?v=i66yyAl5_Jc

All reports use dates, so it is very important that you learn how to convert years, months and days from column values to dates. So you can leaverage Power BI date intelligence.

I want to help you mote but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

4 Replies

speedramps
Super User
1 year ago
He sharmil_28

It is best practice to ALWAYS ALWAYS ALWAYS use dates and calendars for Power BI date intellegence.

Rather that faff about with date, month or year column as val1, val2, val3 etc.

Dvelopers always mess up on 30/31 day months or leap year calculations when they use the val1, val2, val3.

It is much better to develop and test a good calendar table once and use it always.

All reports use dates so please learn about calendar tables

Try this ...

Create a calendar table

Calendar = CALENDAR(DATE(2025,1,1), DATE(2025,12,31))

Add a Day column

Day = FORMAT('Calendar'[Date],"DD")

Add a Period column

Period = FORMAT('Calendar'[Date],"Mmm") & "-" & FORMAT('Calendar'[Date],"YY")

Add a Start of Month column

Start of month = STARTOFMONTH('Calendar'[Date])

Sort the Period column by Start of Month

Insert a Date on your fact table if you dont already have a Date column

Create a One to many relationship from the Calendat{Date] to the Fact[Date]

Crate a Total measure

Total = SUM(Facts[Value]) + 0

Draw a Matrix visual

Test February

Test June

Test July

Learn more for free about Calndar tables here:-

https://www.youtube.com/watch?v=CJ2y6v4mQZk

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works. (you can see from the screen prints it does works)

Thank you
- sharmil_28
  Frequent Visitor
  1 year ago
  speedramps
  Thank you so much for your solution.
  I have [Date] column in Fact table but its only contains the 1st date of the month.
  Any thoughts you have would be helpful. Thank you.
  - speedramps
    Super User
    1 year ago
    Hi again sharmil_28
    
    Are you saying your fact table looks like this with 31 "val" columns
    
    If yes then selcet the start of month column in tyeh trasnform menu Unpivot the other columns
    
    Relace the "val" text with nothing and then Change the column data type to a whole numbner
    
    Add a customer colum to add the start of month and "val" number to get the date
    
    Date.AddDays([Start of month],[Attribute])
    
    Learn more about Power Query here
    
    https://www.youtube.com/watch?v=i66yyAl5_Jc
    
    All reports use dates, so it is very important that you learn how to convert years, months and days from column values to dates. So you can leaverage Power BI date intelligence.
    
    I want to help you mote but your description is too vague. Please write it again.
    
    You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
    
    * Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
    * Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
    * Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
    * Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
    * Remember not to share private data ... we don't want you to get into trouble. ‌‌
    * Please click the thumbs up button for these helpful hints and tips. Thank you.
    
    Learn how to attach data in the forum using OneDrive:-
    * Save your file in a OneDrive folder
    * Right click on the file and click the “Share” blue cloud icon
    * Click the bottom “Copy” button
    * Click” Anyone with link can edit”
    * Click “Can Edit”
    * Click “Can View”
    * Click “Apply” button
    * Click “Copy”
    * Paste the generated link via the forum, email, chat, or any other method.
    * Helpers can then download your data, build a solution and share it back.
    
    Learn how to attach data in the forum using Dropbox:-
    1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
    2. Select File/Folder: Find the file or folder you want to share.
    3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
    4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
    5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.