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.
Let me simplify to the most simple
I have a table with columns
Client, InvoiceDate, InvoiceNumber, InvoiceAmout
There are three rows
ClientA, 23Apr17, 123, $245
ClientB, 10May17, 124, $55.66
Client A, 15May17, 125, $99
I need a Power BI display, table, matrix that shows, Client and first invoice date, invoice count, last invoice date, and total Invoice Amt Total
So I need
Client A, 23Apr17, 2, 15May17,$344
Client B, 10May17,1,10May,$99
////////////////////////
It seems to be a mystery. I know how to do in SQL
Select clientname, min(invoicedate), count(invoice), max(invoicedate), sum(invoiceamount) group by clientname
///////////////////////
But Power BI seems to be a mystery of the ages to do this. Well, at least the online help and forums have never come across such a difficult and complicated scenario
Solved! Go to Solution.
otherwise you can use the summarize feature in dax
https://msdn.microsoft.com/en-us/library/gg492171.aspx
Proud to be a Super User!
otherwise you can use the summarize feature in dax
https://msdn.microsoft.com/en-us/library/gg492171.aspx
Proud to be a Super User!
why dont you use the group by function in power query its super simple or is there a reason for you doing it in dax?
Proud to be a Super User!
But does not that then 'destroy' my data for all subsequent tables, charts, etc.
I have a simple table with heaps of good data. But I cannot show it summarised. A mystery!
you could make a copy of the table if you still need some of the data
or alternatively handle it in your visual, if you
drag your date into the visual just drop down the arrow and choose the earliest in values pane
drag your date in again and select latest in the values pane
drag in your measure invoices and drop down the arrow and select count etc and it will summarize on that level for you.
does that make sense?
Proud to be a Super User!
absolutely make sense, but it does no group by, I get each client with the same first date.... so the grouping does not work AT ALL
I just tried the summarize.... it creates a new table from the base data, but it does not do the first date, last date AT ALL. It simply shows ONE ROW per record... no summarize AT ALL
The issue I have is this: I have given any Power BI guru a simply table of three rows.... I dont want some diatribe, three hour analysis... I just want to know how to do the group by... beccause it JUST DONT WORK... every BI / SQL product on the planet can do 'group by'.... but not Power BI?????????????
I appreciate your trying to help.
It is simple, I have given a sample.... three rows I need 'group by'.... so you can do the summarise or whatever based on that.....
Hooray.... I have it.... the MSDN you sent says this
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
So, I made this
SumTable = SUMMARIZE(ClientDepartureReturn, ClientDepartureReturn[Debtor Code],"mindate",FIRSTDATE(ClientDepartureReturn[Bkn Date].[Date]))
ClientDepartReturn on DebtorCode then group by
This works....
Thanks
Dan
example i have just done this, works perfectly
Proud to be a Super User!
Thanks for help.... the create table (summarize) from the base tables works great... thanks so much
glad you sorted
Proud to be a Super User!
without your help, I would have drunk too much wine.... now I can go home!!!!!!!!!!!
yeah i get it but what you need to show me is what you actually doing, because we missing something here.
please post the visual example i just gave you to do
Proud to be a Super User!
which object are you using?
Proud to be a Super User!
please post what you seeing? and how you put your fields into the object
Proud to be a Super User!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |