Forum Discussion

adriancho_BI's avatar
adriancho_BI
Regular Visitor
2 years ago
Solved

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 r...
Need Help
  • adriancho_BI's avatar
    adriancho_BI
    2 years ago

    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")


Bipin-Lala's avatar
Bipin-Lala
Solution Sage
2 years ago

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?

  • adriancho_BI's avatar
    adriancho_BI
    Regular Visitor
    2 years ago

    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")