The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a fact table that looks like this:
fact_sales:
Comparative | Region | Country | Product | Month | Sales |
2020 Plan | APAC | Thailand | t-shirts | 1 | $ 10.00 |
2020 Plan | APAC | Thailand | shorts | 1 | $ 20.00 |
2020 Plan | APAC | Thailand | snickers | 1 | $ 34.00 |
2020 Plan | APAC | Thailand | balls | 1 | $ 20.00 |
I'm importing this table from an Excel file. Using this data I want to create a new table to use as a dimension (from column "Comparative"). So the new dim table will look like this:
dim_comparative:
Comparative | compYear | compActual |
2020 Plan | 2020 | 1 |
2020 Forecast | 2020 | 1 |
2020 Actual | 2020 | 1 |
2019 $c | 2019 | 0 |
2018 $c | 2018 | 0 |
The "Comparative" column holds the distinct values from the fact table. the "compYear" columns holds the first 4 characters from Comparative, and "compActual" shows a 1 when compYear is the current year, and 0 otherwise.
I've been investigating different alternatives like GENERATE(), DISTINCT() and such, but I wasn't able to figure out how to do this. Also, the new table should be derived from the already loaded table or I should import the data from Excel twice, one for the fact and one for transforming the data into the dim table?
Thanks for your help!
Solved! Go to Solution.
@fleetingImage ,Try like
selectcolumns(distinct(table[Comparative]), "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today())))
or
distinct(selectcolumns(Table, "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today()))))
@fleetingImage ,Try like
selectcolumns(distinct(table[Comparative]), "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today())))
or
distinct(selectcolumns(Table, "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today()))))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
101 | |
101 | |
72 | |
58 |
User | Count |
---|---|
263 | |
121 | |
114 | |
93 | |
85 |