Forum Discussion
Helper IX-AXIS ISSUE
I want to place both these measures in one line and stacked column graph that shows the values of these two measures for all semesters, starting from 01/01/2012 (2012 H1) and ends in 30/06/2023 (2023 H2).
I tried including both Semesters columns in the x-axis. However, when I put the semester column from Table 1 first on the x axis, I get back results from 01/01/2012 (2012 H1) to 01/01/2023 (2023 H1) and when I include the semester column from Table 2 first on the x axis, I get back results from 30/06/2012 (2012 H2) to 30/06/2023 (2023 H2).
3 Replies
- amitchandak
Super User
atziovara , Based on what I got. Create a common date table and join it with both tables on date and use common table in visual, slicer and measure
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bdaWhy Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
atziovara- My data set is like this:
Table Name: TableBI
Wave Company ID Weights Questions Answers Weighted Answers Periods 1 1 0,006 Demand change compared to the previous 6 months -1 -0,006 2022 H2 1 1 0,006 Expected change in demand in the next 6 months 1 0,006 2023 H1 1 1 0,006 Liquidity problems intensity 1 0,006 2022 H2 1 1 0,006 Expected change in employment 0 0 2023 H1 1 2 0,001 Demand change compared to the previous 6 months 1 0,001 2022 H2 1 2 0,001 Expected change in demand in the next 6 months 1 0,001 2023 H1 1 2 0,001 Liquidity problems intensity -1 -0,001 2022 H2 1 2 0,001 Expected change in employment 1 0,001 2023 H1 1 3 0,0057 Demand change compared to the previous 6 months 1 0,0057 2022 H2 1 3 0,0057 Expected change in demand in the next 6 months 0 0 2023 H1 1 3 0,0057 Liquidity problems intensity 0 0 2022 H2 1 3 0,0057 Expected change in employment 0 0 2023 H1 2 1 0,006 Demand change compared to the previous 6 months 1 0,006 2023 H1 2 1 0,006 Expected change in demand in the next 6 months 0 0 2023 H2 2 1 0,006 Liquidity problems intensity 0 0 2023 H1 2 1 0,006 Expected change in employment 0 0 2023 H2 2 2 0,001 Demand change compared to the previous 6 months 1 0,001 2023 H1 2 2 0,001 Expected change in demand in the next 6 months 1 0,001 2023 H2 2 2 0,001 Liquidity problems intensity -1 -0,001 2023 H1 2 2 0,001 Expected change in employment 1 0,001 2023 H2 2 3 0,0057 Demand change compared to the previous 6 months 1 0,0057 2023 H1 2 3 0,0057 Expected change in demand in the next 6 months 0 0 2023 H2 2 3 0,0057 Liquidity problems intensity -1 -0,0057 2023 H1 2 3 0,0057 Expected change in employment 1 0,0057 2023 H2 *Weighted Answers is calculated as: Weights * Answers
**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).
- I have created the calculated column "Period" in Power BI as follows:
Period =
IF(
'TableBI'[Wave] = 1,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2022 H2",
"2023 H1"
),
IF(
'TableBI'[Wave] = 2,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2023 H1",
"2023 H2"
)
))
)
***There are multiple waves in the original data
- I have created the following DAX measure in order to compute the weighted average of Current Demand and the weighted average of Future Demand:
Weighted Average =VAR SelectedCharacteristic = SELECTEDVALUE('TableBI'[Questions])RETURNDIVIDE(SUMX(FILTER('TableBI',NOT(ISBLANK('TableBI'[Weighted Answers])) &&NOT(ISBLANK('TableBI'[Weights])) &&'TableBI'[Questions] = SelectedCharacteristic),'TableBI'[Weights]),SUMX(FILTER('TableBI',NOT(ISBLANK('TableBI'[Weighted Answers])) &&NOT(ISBLANK('TableBI'[Weights])) &&'TableBI'[Questions] = SelectedCharacteristic),'TableBI'[Weights]))- I have placed a slicer with the values of column "Questions". I would like to be able to have the computed weighted average per Period both for Current Demand ("Demand change compared to the previous 6 months") and for Future Demand ("Expected change in demand in the next 6 months") without creating different measures every time I want to compare the weighted average of the answers of two or more questions.
- I created a disconnected table called 'Table2' by duplicating 'TableBI'. I also created another calculated column called 'Period' on the new table with the same formula as the first.
- I included a slicer that takes its values from 'Table2'[Questions].
- I created a new measure on Table2:
Weighted Average Disconnected =CALCULATE([Weighted Average],TREATAS(VALUES('Table2'[Questions]),TableBI[Questions]))- I added a line and stacked column graph. Its Column Values is the Weighted Average (from 'TableBI'), and its Line Values is Weighted Average Disconnected (from 'Table2'). I selected "Expected change in demand in the next 6 months" on the first slicer (from 'TableBI') and "Demand change compared to the previous 6 months" on the second slicer ('Table2').
- I want to have Period on the Shared Axis. I want to be able to show the fact that Current Demand starts on 2022 H2 and ends on 2023 H1, as well as that Future Demand is 6 months ahead, which means that it is blank on 2022 H2, as it starts on 2023 H1and ends on 2023 H2 (when Current Demand is blank). However, when the Shared Axis takes its values from "Period" of 'TableBI' the data of the graph start from 2023 H1 and end on 2023 H2. And when the Shared Axis takes its values from "Period" of 'Table2' the data of the graph start from 2022 H2 and end on 2023 H1.
- My data set is like this:
- atziovara
- My data set is like this:
Table Name: TableBI
Wave Company ID Weights Questions Answers Weighted Answers Periods 1 1 0,006 Demand change compared to the previous 6 months -1 -0,006 2022 H2 1 1 0,006 Expected change in demand in the next 6 months 1 0,006 2023 H1 1 1 0,006 Liquidity problems intensity 1 0,006 2022 H2 1 1 0,006 Expected change in employment 0 0 2023 H1 1 2 0,001 Demand change compared to the previous 6 months 1 0,001 2022 H2 1 2 0,001 Expected change in demand in the next 6 months 1 0,001 2023 H1 1 2 0,001 Liquidity problems intensity -1 -0,001 2022 H2 1 2 0,001 Expected change in employment 1 0,001 2023 H1 1 3 0,0057 Demand change compared to the previous 6 months 1 0,0057 2022 H2 1 3 0,0057 Expected change in demand in the next 6 months 0 0 2023 H1 1 3 0,0057 Liquidity problems intensity 0 0 2022 H2 1 3 0,0057 Expected change in employment 0 0 2023 H1 2 1 0,006 Demand change compared to the previous 6 months 1 0,006 2023 H1 2 1 0,006 Expected change in demand in the next 6 months 0 0 2023 H2 2 1 0,006 Liquidity problems intensity 0 0 2023 H1 2 1 0,006 Expected change in employment 0 0 2023 H2 2 2 0,001 Demand change compared to the previous 6 months 1 0,001 2023 H1 2 2 0,001 Expected change in demand in the next 6 months 1 0,001 2023 H2 2 2 0,001 Liquidity problems intensity -1 -0,001 2023 H1 2 2 0,001 Expected change in employment 1 0,001 2023 H2 2 3 0,0057 Demand change compared to the previous 6 months 1 0,0057 2023 H1 2 3 0,0057 Expected change in demand in the next 6 months 0 0 2023 H2 2 3 0,0057 Liquidity problems intensity -1 -0,0057 2023 H1 2 3 0,0057 Expected change in employment 1 0,0057 2023 H2 *Weighted Answers is calculated as: Weights * Answers
**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).
- I have created the calculated column "Period" in Power BI as follows:
Period =
IF(
'TableBI'[Wave] = 1,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2022 H2",
"2023 H1"
),
IF(
'TableBI'[Wave] = 2,
IF(
('TableBI'[Questions] = "Demand change compared to the previous 6 months" ||
'TableBI'[Questions] = "Liquidity problems intensity"),
"2023 H1",
"2023 H2"
)
))
)
***There are multiple waves in the original data
- I have created the following DAX measure in order to compute the weighted average of Current Demand and the weighted average of Future Demand:
Weighted Average =VAR SelectedCharacteristic = SELECTEDVALUE('TableBI'[Questions])RETURNDIVIDE(SUMX(FILTER('TableBI',NOT(ISBLANK('TableBI'[Weighted Answers])) &&NOT(ISBLANK('TableBI'[Weights])) &&'TableBI'[Questions] = SelectedCharacteristic),'TableBI'[Weights]),SUMX(FILTER('TableBI',NOT(ISBLANK('TableBI'[Weighted Answers])) &&NOT(ISBLANK('TableBI'[Weights])) &&'TableBI'[Questions] = SelectedCharacteristic),'TableBI'[Weights]))- I have placed a slicer with the values of column "Questions". I would like to be able to have the computed weighted average per Period both for Current Demand ("Demand change compared to the previous 6 months") and for Future Demand ("Expected change in demand in the next 6 months") without creating different measures every time I want to compare the weighted average of the answers of two or more questions.
- I created a disconnected table called 'Table2' by duplicating 'TableBI'. I also created another calculated column called 'Period' on the new table with the same formula as the first.
- I included a slicer that takes its values from 'Table2'[Questions].
- I created a new measure on Table2:
Weighted Average Disconnected =CALCULATE([Weighted Average],TREATAS(VALUES('Table2'[Questions]),TableBI[Questions]))- I added a line and stacked column graph. Its Column Values is the Weighted Average (from 'TableBI'), and its Line Values is Weighted Average Disconnected (from 'Table2'). I selected "Expected change in demand in the next 6 months" on the first slicer (from 'TableBI') and "Demand change compared to the previous 6 months" on the second slicer ('Table2').
- I want to have Period on the Shared Axis. I want to be able to show the fact that Current Demand starts on 2022 H2 and ends on 2023 H1, as well as that Future Demand is 6 months ahead, which means that it is blank on 2022 H2, as it starts on 2023 H1and ends on 2023 H2 (when Current Demand is blank). However, when the Shared Axis takes its values from "Period" of 'TableBI' the data of the graph start from 2023 H1 and end on 2023 H2. And when the Shared Axis takes its values from "Period" of 'Table2' the data of the graph start from 2022 H2 and end on 2023 H1.
- My data set is like this:
