Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi, I'm new to calculated tables. But I think I need this as my raw dataset is not expressed the way I want it. Therefore I have two questions related to below raw data screenshot:
I would like to build a new table with following type of columns:
1. One type of coloumn which returns the same parameter for instance 'Project code' but it should not return 'distinct' as I have multiple values of these.
2. A bit more advanced: Can I create a column which returns the dates in columns C if column B has a specific name like 'Budget approved'. In a nut sheel I would like to have a column named 'Budget approved' and the dates as rows.
I'm able to create all this in a pivot table in excel, but I would like to let powerBI handle it instead.
Thanks
Kristoffer
Solved! Go to Solution.
Hi @Kristofferaabo,
Could you try the formula below to see if it works?
Table = SUMMARIZE ( 'Table1', 'Table1'[project Code], 'Table1'[last_name], "Budget approved", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" ) ), "Budget submitted", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" ) ) )
Regards
Hi @Kristofferaabo,
If I understand you correctly, you should be able to use the formula below to create a new calculate table to get the expected result in your scenario.
Table = SUMMARIZE ( 'Table1', 'Table1'[project Code], 'Table1'[last_name], "Budget approved", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" ) ) )
Note: You'll need to replace 'Table1' with your real table name.
Regards
Hi @v-ljerr-msft, this is really cool. Exactly what I was looking for. Perhaps a stupid question, But I was trying to include another column as I wanted a couple of these columns in my data (basically because I want to calculate time between each columns).
I tried something lik ethis, but it gives me a "TRUE/FALSE error"
Table = SUMMARIZE ( 'Table1', 'Table1'[project Code], 'Table1'[last_name], "Budget approved", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" ),
"Budget submitted", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" )
) )
Hi @Kristofferaabo,
Could you try the formula below to see if it works?
Table = SUMMARIZE ( 'Table1', 'Table1'[project Code], 'Table1'[last_name], "Budget approved", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" ) ), "Budget submitted", CALCULATE ( FIRSTNONBLANK ( 'Table1'[actual_date], 1 ), FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" ) ) )
Regards
YOU ARE WIZARD! Thanks
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
105 | |
77 | |
35 | |
35 |
User | Count |
---|---|
158 | |
103 | |
71 | |
65 | |
53 |