Forum Discussion
Create New Column referencing other table's column
Hello! Please your hel in figuring out how to Create a New Column referencing other table's column
In a single record table, I want to count, how many times repets a certain column per this single record column/table
table 1: ID table, one row per ID
table 2: fact table, many transactions for every ID
I need to count how many "products" are associated per ID in a new column, because my next step is to create a range column to use in a later biz.
THANK IN ADVANCE FOR THE HELP!
My goal is to see something like this:
| ID | Q_PRODS | Q_PRODS_GROUP |
| A | 0 | 0 |
| B | 1 | 1 |
| C | 2 | 2 |
| D | 55 | >=3 |
Thank you for the response, but I already have the count, what I actually need is to create the group or category using something like SWITCH() OR IF() but when I visualize it, it only shows me the last category ">=3". I already checked the data format as WHOLE NUMBER with zero decimals
e.g.:Column =IF( MAX('dimbakery'[count]) = 0, "0",
IF(MAX('dimbakery'[count]) = 1 , "1",
IF(MAX('dimbakery'[count]) =2 , "2" ,
IF(MAX('dimbakery'[count]) >= 3 ,">=3"))))
or
Column =
SWITCH (
MAX('dimbakery'[count]),
0, "0",
1, "1",
2, "2",
">=3")
2 Replies
Hi adriancho_BI,
Let's assume the below mentioned dimBakery and factBakery tables as examples -
- You must be having a one-to-many relationship flowing from dimBakery to factBakery table
- To count the number of records for each dim table ID present in the fact table, we can use the following formula
Bakery Sale Record Count = CALCULATE(COUNT(factBakery[ID]), USERELATIONSHIP(dimBakeryItems[ID], factBakery[ID]))- This will create a column in dimBakery table, counting the number of transactions in factBakery table.
In general, if you want to refer to a column from another table for calculation, you can use the RELATED function, provided there is a relationship between the 2 tables.
Or you can also use LOOKUPVALUE as well, depending upon requirements.
Let me know if this helps you out with your requirements. If it doesn't, can you please share some sample data or your pbix file, which can then help me provide an exact solution for your requirements?
Thank you for the response, but I already have the count, what I actually need is to create the group or category using something like SWITCH() OR IF() but when I visualize it, it only shows me the last category ">=3". I already checked the data format as WHOLE NUMBER with zero decimals
e.g.:Column =IF( MAX('dimbakery'[count]) = 0, "0",
IF(MAX('dimbakery'[count]) = 1 , "1",
IF(MAX('dimbakery'[count]) =2 , "2" ,
IF(MAX('dimbakery'[count]) >= 3 ,">=3"))))
or
Column =
SWITCH (
MAX('dimbakery'[count]),
0, "0",
1, "1",
2, "2",
">=3")
