Forum Discussion

ZanneMari's avatar
ZanneMari
Frequent Visitor
1 year ago
Solved

Convert Unoque ID to a User friendly ID

I have a Audit tool that gives each audit a unique ID e.g audit_508eed5c86ec4f92b6a82b15820a3273 and one of the questions in this audit is "What is the Order Number" e.g Order 0001. I would like to create a colum similar to how each row has the above audit ID but reference the Order number. Currently the order number is only referenced to the one row. 

 

Therefor I can search the order number 0001 and retrieve all the rows affiliated to that order rather than filtering on audit ID.

 

 

Need Help

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    ZanneMari 

    You can create a calculated column

    Order Number Ref = 
    CALCULATE(
    FIRSTNONBLANK('YourTable'[Order Number], 1),
    ALLEXCEPT('YourTable', 'YourTable'[audit_id])
    )

    You can use this new column in your report visuals.
    Create slicers or filters based on the Order Number so that users can search and filter all rows affiliated with a given Order Number.

     

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • saud968's avatar
    saud968
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Here's a step-by-step approach to achieve this in Power BI:

    Create a New Column:

    In Power BI, go to the Power Query Editor.

    Right-click on the table and select "Add Column."

    Choose "Custom Column."

    In the formula bar, enter the following expression:

    Order Number = Table.Column([Table Name], "Order Number")
    Replace [Table Name] with the actual name of your table.

    This will create a new column called "Order Number" that contains the value from the "Order Number" column for each row.

    Filter and Group Data:

    Now, you can use the "Order Number" column to filter and group your data in Power BI.
    For example, you can create a visual and filter it by the "Order Number" to see all the details related to a specific order.
    You can also group data by the "Order Number" to calculate summaries and trends for each order.

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!


  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, ZanneMari 

    I create a sample table:

     

    You can create a calculated column, try the following DAX expression

    OrderNumberColumn = 
    CALCULATE(
        MAX('Table'[Order Number]),
        ALLEXCEPT('Table', 'Table'[audit_id])
    )

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.