Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Have a table with product to produce information
basing on columns 'Start Prod Date', 'Product to Produce' and 'Type' I need to create columns 'Changes' and 'Number of Times'
Start Prod Date | ProductToProduce | Type | Changes | Number Of Times |
01-02-18 | Chocolate A | A | ||
01-02-18 | Chocolate A | A | AB | 1 |
01-02-18 | Chocolate B | B | ||
01-02-18 | Chocolate B | B | BA | 1 |
05-02-18 | Chocolate A | A | AC | 1 |
05-02-18 | Chocolate C | C | ||
05-02-18 | Chocolate C | C | ||
05-02-18 | Chocolate C | C | ||
09-02-18 | Chocolate C | C | CB | 1 |
09-02-18 | Chocolate B | B | BA | 1 |
09-02-18 | Chocolate A | A | ||
09-02-18 | Chocolate A | A |
Solved! Go to Solution.
@slyfox,
Add an index column in your table in Query Editor of Power BI Desktop.
Then create the following columns in your table.
Column = CALCULATE(FIRSTNONBLANK(Table[Type],1),FILTER(Table,Table[Index]=EARLIER(Table[Index])+1))
Changes = IF(Table[Type]=Table[Column] || ISBLANK(Table[Column]),BLANK(),CONCATENATE(Table[Type],Table[Column]))
Number Of times = CALCULATE(COUNTA(Table[Changes]),ALLSELECTED(Table[Index]))
Regards,
Lydia
@slyfox,
Add an index column in your table in Query Editor of Power BI Desktop.
Then create the following columns in your table.
Column = CALCULATE(FIRSTNONBLANK(Table[Type],1),FILTER(Table,Table[Index]=EARLIER(Table[Index])+1))
Changes = IF(Table[Type]=Table[Column] || ISBLANK(Table[Column]),BLANK(),CONCATENATE(Table[Type],Table[Column]))
Number Of times = CALCULATE(COUNTA(Table[Changes]),ALLSELECTED(Table[Index]))
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |