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.
Hello,
I'm wondering if someone can help in what I percieve to be a simple query,
I have a table which I have created in DAX using SELECT COLUMNS and CALCULATETABLE.
The result of thisis the following table:
As you can probably see in this table all rows share the same [Contract] in column 1, I would like this to all appear in one row rather than 3 rows. usually I would do this in power query and unpivot etc. but as this is a calculated table is there a way to do this in DAX?
As always any help or ideas are always appricated.
Dobby Libr3
Solved! Go to Solution.
First GIndex has different values in all three columns.
Second, there should be aggregation function to group function like Min, Max on all other columns
You can also try summarize. example
SUMMARIZE(
data,
data[ID],
"max_date",
MAX( 'Date'[Date]),
"Max_Col", max(data[Col1])
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
First GIndex has different values in all three columns.
Second, there should be aggregation function to group function like Min, Max on all other columns
You can also try summarize. example
SUMMARIZE(
data,
data[ID],
"max_date",
MAX( 'Date'[Date]),
"Max_Col", max(data[Col1])
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @amitchandak ,
Thanks for the response, sorry the about the GIIndex column, it was only there for testing the correct information had pulled through.
SUMMARIZE works perfectly! Thanks for explaining I'll be using this again.
Breaks (Next 3) = SUMMARIZE(
'Subscription - Break',
'Subscription - Break'[SBQQ__Contract__c],
"M Break Date 1", MAX('Subscription - Break'[Break Date 1]),
"M Min Notice 1", MAX('Subscription - Break'[Min. Notice 1]),
"M Penalty 1", MAX('Subscription - Break'[Penalty 1]),
"M Option 1", MAX('Subscription - Break'[Option 1]),
"M Break Date 2", MAX('Subscription - Break'[Break Date 2]),
"M Min Notice 2", MAX('Subscription - Break'[Min. Notice 2]),
"M Penalty 2", MAX('Subscription - Break'[Penalty 2]),
"M Option 2", MAX('Subscription - Break'[Option 2]),
"M Break Date 3", MAX('Subscription - Break'[Break Date 3]),
"M Min Notice 3", MAX('Subscription - Break'[Min. Notice 3]),
"M Penalty 3", MAX('Subscription - Break'[Penalty 3]),
"M Option 3", MAX('Subscription - Break'[Option 3])
Regards
Dobby Libr3
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |