Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
currently i`m developing a tabular model with a bunch of DAX logic in it. One of the requirements is that for the years 2022 & 2023 some additional quantities must be added to the base measure.
For the year 2022 it would be 26 pcs per month
For the year 2023 it would be 27,5 pcs per month
If i only select the single years everything seems to be fine, the adjusted measure is defined like this:
VAR __Market =
CALCULATE(
SUM( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)
RETURN
IF(
HASONEVALUE( 'v_DimMarketActivity'[ChartType] ),
IF(
SELECTEDVALUE( 'v_DimDate'[Year] ) = 2022,
[AdditionalTestValue2022],
IF(
SELECTEDVALUE( 'v_DimDate'[Year] ) = 2023,
[AdditioanlTestValue2023],
__Market
)
)
)
Now the users also can mix several previous years with the years that have the addition. F. ex. year 2021 & 2022. In that case it sums the values for both years but for 2022 without the addition of 26 pcs. F.ex. the total value for January 2021 is 3609 and for January 2022 without the addition is 3876. The adjusted value for 2022 with the additional pieces for January is 3902. Now if i select the two years i would expect that it uses for January 2022 the value 3902 instead of 3876 and creates the sum 7511 pcs. Instead it still takes the value for Jan 2022 3876 and creates the sum for both Jan 2021 and Jan 2022 = 7485. The question would be how can i make sure that it uses the adjusted when selecting the adjusted years and a older year without the added pieces.
Thank you in advance!
Solved! Go to Solution.
Hi @atibu
Please try
CountMeasure =
VAR AddCount2022 = 26
VAR AddCount2023 = 27.5
RETURN
SUMX (
SUMMARIZE ( 'v_DimDate', 'v_DimDate'[Year], 'v_DimDate'[Month] ),
VAR __Market =
CALCULATE (
SUM ( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)
RETURN
IF (
HASONEVALUE ( 'v_DimMarketActivity'[ChartType] ),
SWITCH ( 'v_DimDate'[Year], 2022, AddCount2022, 2023, AddCount2023 ) + __Market
)
)
However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )
Hi @atibu
Please try
CountMeasure =
VAR AddCount2022 = 26
VAR AddCount2023 = 27.5
RETURN
SUMX (
SUMMARIZE ( 'v_DimDate', 'v_DimDate'[Year], 'v_DimDate'[Month] ),
VAR __Market =
CALCULATE (
SUM ( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)
RETURN
IF (
HASONEVALUE ( 'v_DimMarketActivity'[ChartType] ),
SWITCH ( 'v_DimDate'[Year], 2022, AddCount2022, 2023, AddCount2023 ) + __Market
)
)
However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )
Hi @tamerj1,
thanks! that solved my issue - really appreciate your help.
However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )
This is just a precondition required by the business but doesnt interfere with anything else.
Thanks again!
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
10 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |