Forum Discussion
Create calculated column in Table2 that can be sliced using fields from Table1
| My objective is to be able to create a visual (a column chart) with Table2[Arbitrary date] as the X-axis and the Table2[Calculated column] as the Y-axis. I need to be able to have this chart affected by slicers from Table1. For example, the calculated column in Table2 should change values when Table1[color] is used as a slicer and "Yellow" is selected. Everything I've researched indicates that a relationship is required (maybe that's not the case), but I don't know how one could create a relationship between the two tables that accomplished my objective. |
Here is an example Table1:
| Serial number | Location | Product type | Color | Date received | Date processed | Date shipped |
| 101 | TX | Used | Black | 10/1/2022 | 10/31/2022 | 11/30/2022 |
| 102 | TX | New | Brown | 10/2/2022 | 11/1/2022 | 12/1/2022 |
| 103 | TX | Refurbished | Yellow | 10/3/2022 | 11/2/2022 | 12/2/2022 |
| 104 | TX | Used | Pink | 10/4/2022 | 11/3/2022 | 12/3/2022 |
| 105 | TX | New | Black | 10/5/2022 | 11/4/2022 | 12/4/2022 |
| 106 | AR | Refurbished | Brown | 10/6/2022 | 11/5/2022 | 12/5/2022 |
| 107 | AR | Used | Yellow | 10/1/2022 | 10/31/2022 | 11/30/2022 |
| 108 | AR | New | Pink | 10/2/2022 | 11/1/2022 | 12/1/2022 |
| 109 | AR | Refurbished | Black | 10/3/2022 | 11/2/2022 | 12/2/2022 |
| 110 | AR | Used | Brown | 10/4/2022 | 11/3/2022 | 12/3/2022 |
| 111 | AR | New | Yellow | 10/5/2022 | 11/4/2022 | 12/4/2022 |
| 112 | AR | Refurbished | Pink | 10/6/2022 | 11/5/2022 | 12/5/2022 |
Here is an example Table2, column "Calculated column" being the crux of my question:
| Arbitrary date | Calculated column |
| 10/2/2022 | =calculate(… |
| 10/9/2022 | =calculate(… |
| 10/16/2022 | =calculate(… |
Here is what I've tried (with no success) for the calculated column:
| =calculate( count(table1[serial number]) ,table1[date received] > table2[arbitrary date], table1[date processed] > table2[arbitrary date] ) |
Hi jbstephenson,
I'm this case you need to create a measure to make the calculation dinamically try the following.
Measure =
=countrows(FILTER(table1 ,table1[date received] >MAX( table2[arbitrary date]) & & table1[date processed] > MAX( table2[arbitrary date] ))
4 Replies
- MFelix
Super User
Hi jbstephenson ,
To create a relationship between tables you need to follow this guidance.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
But in this case I have some doubts you refer that you want to have a filter on the colour? does this means that you want the calculated table to be dinamic based on selection?
Yes, I need it to be dynamic based on selection
- MFelix
Hi jbstephenson,
I'm this case you need to create a measure to make the calculation dinamically try the following.
Measure =
=countrows(FILTER(table1 ,table1[date received] >MAX( table2[arbitrary date]) & & table1[date processed] > MAX( table2[arbitrary date] ))
