Power BI Date Dimension: How to create it?
Are you using a dataset in Power BI that contains date fields which you need to use for analysis and creating reports? You need to use a Date Dimension in your model. A date dimension is an essential table in a data model that allows us to analyze performance more effectively across different time periods. It should be included in every dimensional model that contains a date or requires date intelligence as part of the analysis. In this session Shahab will introduce dim date and the necessity of using it and will explain how to create a dimension table in Power BI using different methods, including M Power Query, SQL script, DAX, Bravo Extension, and even the new tool ChatGPT. 𝙋𝙚𝙧𝙨𝙞𝙖𝙣 𝙋𝘽𝙄𝙐𝙂- 𝙈𝙞𝙘𝙧𝙤𝙨𝙤𝙛𝙩 𝘾𝙤𝙢𝙢𝙪𝙣𝙞𝙩𝙮: https://bit.ly/3IAg7xT 𝙇𝙞𝙣𝙠𝙚𝙙𝙄𝙣: https://bit.ly/32tGkif 𝙏𝙚𝙡𝙚𝙜𝙧𝙖𝙢 𝘾𝙝𝙖𝙣𝙣𝙚𝙡: https://t.me/PersianPBIUG 𝙔𝙤𝙪𝙏𝙪𝙗𝙚: https://bit.ly/3hk20RL Language: Persian - Farsi
Measure vs Linked data from other Dimension tables
Hi all, I noticed that whenever I pull in a measure with some calculation, table visual takes long time to load or not even loaded. But as soon as removed those linked data from other Dimension tables, measure immediately loaded. I am not sure what is the related linkage here regarding the issue as - I created measure that never use data from linked tables; - Dimension tables only pass on description for presenting visual data - and Relation most between Dimension and Fac is 1:n Any suggestion or experience on such observation that I brought up is highly appreciated Thanks and best regards, R.
DAX that works with/rolls up to different dimensions
Hello All, I have had a look for this topic and can't find any discussion about it so i'm either the first to ask or my search paremeters are shocking. Either way, I need some help! I have a requirement to compare a sum total to the same day in the previous week to provide a week on week variance. This is an amendment to an existing report in my business that was originaly aggregating numbers per week. Now the requirements is to look at these figures each day of the week but it still needs to be aggreagated for the week. This is where i am struggling as I can either get it to work for each day or for the week as a total. I've tried simply using DATEADD but this always results in an error (returns multiple results when it expects one). My source data is an aggregated view that appears as follows: This is linked with a DIM_DATE table with many columns but the ones used in my report are the Calendar_Date (for the relationship to the KPIs table shown above), [Start of Week], [FISCAL_WEEK], [WEEK_DAY_NAME]. The normal SUM DAX for the Gross Orders per day is fine (as you'd expect), but my WOW% comparison is being a pain: My current DAX is as follows: Orders WOW Growth = VAR SumOrders = SUMX ( SALES_KPIS, SALES_KPIS[GrossOrders] ) VAR PreviousFiscalWeek = MAX ( DIM_DATE[FISCAL_WEEK] ) - 1 VAR WeekDayName = SELECTEDVALUE ( DIM_DATE[WEEK_DAY_NAME] ) VAR OrdersPreviousWeekDay = CALCULATE ( SUM ( SALES_KPIS[GrossOrders] ), FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek ), DIM_DATE[WEEK_DAY_NAME] = WeekDayName ) VAR OrdersPreviousWeek = CALCULATE ( SUM ( SALES_KPIS[GrossOrders] ), FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek ) ) VAR GrowthPercentage = IFERROR ( ( SumOrders - OrdersPreviousWeekDay ) / OrdersPreviousWeekDay, BLANK () ) RETURN GrowthPercentage If i swap out the variable 'OrdersPerviousWeekDay' with 'OrdersPreviousWeek', i do get the figure for the week but it also gives me the weekly total for each day which is not what i need! Is there anybody on here that can help point me in the right direction. I've tried using a combination of SWITCH and IF commands as well but return errors each time! I've been trying to get this to work for a day and its now time to raise the white flag. Any help will be appreciated! Thanks, Chris
create boolean out of status column with one status relevant
Hello, I'm trying to create a new dimension in the form of a boolean value. The table looks like the following: cart_id payment_status 1 pending 1 authorised 1 captured 2 pending 2 failed with the additional column "success" I wish to mark all "cart_id" with '1' if their payment was authorised and all others with '0'. The challenge is that one cart_id can have many other payment_status but the only one I care for is "authorised". cart_id payment_status success 1 pending 1 1 authorised 1 1 captured 1 2 pending 0 2 pending 0 Would be super happy for any advise on how to create this dimension in Power BI Thanks in advance
DAX historical dimension using slicer
Hi, I'm not sure if the subjects reflects my question perfectly but I want to have a dynamic dimension based on the slicer. A small example below: One dimension table with two projects: On dimension table with historical info on the phase status of the project A fact table with the cost of the project I created two measures: one for the amount filtered on the date slicer and one to get the last status (phase) of the project: Amount m = calculate(sum('Cost fact'[Amount]),filter(Kalender,Kalender[Datum]<=max('Cost fact'[Date]))) Phase (hist) = calculate(max('Project (historical)'[Phase]),filter('Project (historical)','Project (historical)'[Date]=max('Cost fact'[Date]))) This does work correct as long as I am showing the project in the matrix but that is not wat I want January: Februari: The challange I'm facing is when not having the project on the visual. How can I get the amount current phase in total without the project. Currently it would show: This absolutly makes sense as the measure looks at the max phase which is in this case 2 but it's not what i want to achieve. Please help me.
