Check if rows contain a string value against an unrelated table
Hi, I have two tables in my dataset: Table1 is my transactions which have a description that looks like this: and Table2 is my legend table, where I define what each transaction belong to what category: My goal is to categorize each transaction based on the legend by looking for a piece of description column in the transaction column. So if a transaction contains "Desk", the column I'm going to create shows "Office Supplies". Here is what I've tried: I used a new custom column in Table 1 to check for 3 values and return true if they returned a value: List.Transform ( {"Desk", "Payroll", "Chair"}, (substring) => Text.Contains([Description], substring)) This returns a list of TRUE or FALSE based on the three examples I gave it. Potentially this can be used to get the first row that returns true and use that as a reference for the second column? I tried to do it in Excel, and essentially I had to use SEARCH fonction with INDEX and MATCH to get the category from a different table. My issue is that I have two tables that are not related and I'm not sure how to do a search for a piece of string for every row. I'm pretty new to PowerBI and I need some support please. TIA!
Rank orders by value with multiple lines per order
Hello community, hope you can help me out ... I've been trying to create a ranking of sales orders where some of them can have multiple line in the fact table since a given order can contain multiple products. I have a really simple model, a fact table SalesOrders with two dimensions Date and Products, and two disconnected tables one for measures and one for rank grouping: This is what my fact table looks like: Then I created the following measures: (credit to Enterprise DNA since I took this formula from them ... ) As a result of the measures above, I get the following working correctly: So far so good ... the problem is what I want to add Products into the table or matrix and still see how the orders rank. As you can see in the table below, it is my DAX formula is ranking the orders within each product category / name: All that said, I really don't know how to modify my measure to have the ranking be like in the first set of tables but still show the product level data in the table... expecting something like this: The top 3 orders continue to be 10, 9 and 5, but I'm now showing the products each has with their values. Hope the explanation of my problem and what I've done so far was clear. I'm also adding the pbix file for this example. Sample pbix file I really appreciate any help you can provide. Thanks EstebanGroup table in terms of date column and specific value of another column
Hello all, I have a problem which sounds so easy but as I'm a newbie I can't manage it and need your expertise to help me please: As a sample you can have a look at the table below: I need to remove all the rows which have not Main Category value on their date (in this example I should remove rows 9,10,11), The main category value here is the beginning point for my process per day and if it does not exist in a day, I should remove all the other records on that specific date or ignore them could you please let me know how can I make a DAX code or M-query to fix it? as my data are thousands or millions, needs to have the fastest one. Many thanksPercentage of a group
Hi, I feel like this might be a little more straight forward than I think and I'm overthinking it. I have a data set that looks something like this: Program application # met standard binary A 1 1 B 2 0 B 3 1 B 4 0 C 5 1 C 6 1 C 7 0 D 8 1 What I am looking for is the percentage of programs that are meeting the standard. So if I were to calculate for the above, 75% of programs are meeting the standard, programs A, C and D are meeting the standard. I believe it's a calculation but I can't figure it out Any help that can be provided would be appreciated. Thanks in advance, VanlangGroup values together based on field value and create new combined value
Hi looking for a way to do this without creating a separate Table or a calculated Column. But looking for way to create a Measure that would combine a Sales Category for only certain Customers into a new combined value. See below, Lets lets say Customer Bill Gates has sales in multiple Sales Category and I'm looking to create a Measure that says" If Customer="Bill Gates" then combine Sales Category, else show Sales Category. So looking for 1. Ability to statically enter Certain\multiple Customers to apply this to 2. Ability to statically list certain\multiple Sales Categories that I want to combine and the other Sales category will just be listed as normal. 3. Name this new comobined value any name that I want. Anyone have any ideas on how to do this with a measure? See example below. Before results: Customer Sales Category Sales$ Bill Gates Software 1,000 Bill Gates Hardware 500 Bill Gates Internet 200 Bill Nine Science 200 Tiger Woods Golf 99999 Tiger Woods Promo 5 Expected Results: ( I would put in the Measure 2 things, Customer Name = "Bill Gates", and Sales Category to combine = "Software" and "Hardware" ) and have the new Combined Value be called "Combined" Customer Sales Category Sales$ Bill Gates Combined 1,500 Bill Gates Internet 200 Bill Nine Science 200 Tiger Woods Golf 99999 Tiger Woods Promo 5
