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! I would like to create a new table that summarizes Prod. and Cust. depending on last Date (for both Prod. and Cust).
Please see an example below.
For the second Prod. there is the same date for different Cust. that's why I thought to use FirstNonBlank.
Prod. | Cust. | Date |
1 | a | dt1 |
1 | a | dt2 |
1 | b | dt3 |
2 | a | dt1 |
2 | a | dt4 |
2 | c | dt4 |
Desired result in New Table:
Prod. | Cust. | Date (max) |
1 | b | dt3 |
2 | a | dt4 |
Could anyone please help me? Many thanks in advance, Kind regards, est gas
Solved! Go to Solution.
Hi @estgas
Go to Modelling Tab>>>>NEW TABLE
New Table = SUMMARIZE ( TableName, TableName[Prod.], "Last Date", MAX ( TableName[Date] ), "Last Date Customers", CALCULATE ( FIRSTNONBLANK ( TableName[Cust.], TableName[Cust.] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) ) )
Hi @estgas
Go to Modelling Tab>>>>NEW TABLE
New Table = SUMMARIZE ( TableName, TableName[Prod.], "Last Date", MAX ( TableName[Date] ), "Last Date Customers", CALCULATE ( FIRSTNONBLANK ( TableName[Cust.], TableName[Cust.] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) ) )
Many Thank Zubair! Exactly what I was looking for!
By the way I replaced SUMMARIZE with SUMMARIZECOLUMNS assuming that is preferred according to conclusion of https://www.sqlbi.com/articles/introducing-summarizecolumns/
HI @estgas
Yes I believe
SummarizeColumns
or
combination of Summarize with AddColumns
is much faster than mere Summarize
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |