Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello When i combine Variables with Switch statement it doesn’t work. But instead of variables I write entire calculation it work perfectly.
Below are 2 scenario of my measure, The first scenario doesn’t work if i use variable, but second work perfectly if i don't use variables. (The Dark Grey Bar's are the one causing issue)
Can anyone please help what i am doing wrong in first scenario.
merry christmas
scenario 1 (Incorrect) :-
Solved! Go to Solution.
Hi @Anonymous ,
check this out.
Test Measure2 =
CALCULATE (
CALCULATE (
SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
FILTER (
'Calendar',
CONTAINS (
VALUES ( Axis_Table[Values] ),
Axis_Table[Values], SWITCH (
SELECTEDVALUE ( Axis_Table[Type]),
"Year", 'Calendar'[Year_Cal],
"Quarter", 'Calendar'[Quater-Year],
"Month", 'Calendar'[MonthYear_Cal]
)
)
)
)
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous ,
I'd like to suggest you use in operator to replace TREATAS function, it also works to compare unrelated fields and list of values:
Total_workforce_FLC EURO(M/Y-1)2 =
IF (
HASONEVALUE ( 'Axis_Table'[Type] ),
CALCULATE (
CALCULATE (
SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
FILTER (
'Calendar',
SWITCH (
SELECTEDVALUE ( Axis_Table[Type] ),
"Year", 'Calendar'[Year_Cal] IN VALUES ( 'Axis_Table'[Values] ),
"Month", 'Calendar'[MonthYear_Cal] IN VALUES ( 'Axis_Table'[Values] ),
"Quarter", 'Calendar'[Quater-Year] IN VALUES ( 'Axis_Table'[Values] )
)
)
)
)
Regards,
Xiaoxin Sheng
Hi @Anonymous,
Please check the following blog to know more about variable function usage in Dax calculations:
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Appriciate your responce.
If variable doesn’t work, is there anything help me out to make my DAX expression efficient?
Thanks,
Saad
HI @Anonymous ,
Can you please share some dummy data for test? It is hard to modify your formula without any sample data.
In addition, you can also try to use the following formula if it suitable for your requirement:
Total_workforce_FLC EURO(M/Y-1) =
CALCULATE (
CALCULATE (
SUM ( MOR_Data[Current month Fully Loaded Costs Euro] )
+ SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
MOR_Data[STATUS] <> "Contingent",
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
FILTER (
ALLSELECTED ( MOR_Data ),
SWITCH (
SELECTEDVALUE ( 'Axis_Table'[Type] ),
"Year", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Year_Cal] ),
"Quarter", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Quater-Year] ),
"Month", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[MonthYear_Cal] ),
FALSE ()
)
)
)
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
VAR stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.
https://docs.microsoft.com/en-us/dax/var-dax
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hello, @mwegener ,
Thanks for your reply. I got the understanding of how variable is evaluated.
Is there any way i can optimize my DAX, without creating any external measure ?
Thanks,
Saad
Hi @Anonymous ,
I played with your idea a bit. It was fun. 😄
You may download my PBIX file from here.
Hope this helps.
You might also be interested in this.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hello @mwegener
Thanks for sharing PBIX file, that is the same concept i am working on (Dynamic axis base on Month, Quarter and Year).
I have used a different way to achieve it.
Can you help me out in a measure i have created? Juts to simplify it or write it in an efficient manner without using any external measure. I have uploaded my PBIX, below is the link. (We Transfer file sharing)
I have created a "Test Measure" in which I have to repeat same calculation 3 times (Just for Year, Quarter and Month). Can minimize it to just once? using same concept and tables.
@v-shex-msft appriciate your support as well 🙂
Thanks
HI @Anonymous ,
After I check your sample file, I found your attribute table does not link to the original table, so your attribute filter does not interact with value table records. I'd like to suggest you take a look at the following blog to know how to achieve dynamic attribute filter:
Dynamic Attributes In A Power BI Report
Regards,
Xiaoxin Sheng
hello @v-shex-msft
I have achieved Dynamic axis by (Month Quarter and Year) by slicer. From below you can download PBIX file.
https://wetransfer.com/downloads/b020c2fa16ff61a3d38f859f0ce7cca120191230111741/3f18b3
Instead of connecting attribute table to original table, I have use function “TREATAS”
Everything working fine, Just need help in optimizing measure “Total_workforce_FLC EURO(M/Y-1)” . I have to repeat same calculation 3 times. For year quarter and month, is there any way to avoid that using concept I have use?
Thanks
Hi @Anonymous ,
I'd like to suggest you use in operator to replace TREATAS function, it also works to compare unrelated fields and list of values:
Total_workforce_FLC EURO(M/Y-1)2 =
IF (
HASONEVALUE ( 'Axis_Table'[Type] ),
CALCULATE (
CALCULATE (
SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
FILTER (
'Calendar',
SWITCH (
SELECTEDVALUE ( Axis_Table[Type] ),
"Year", 'Calendar'[Year_Cal] IN VALUES ( 'Axis_Table'[Values] ),
"Month", 'Calendar'[MonthYear_Cal] IN VALUES ( 'Axis_Table'[Values] ),
"Quarter", 'Calendar'[Quater-Year] IN VALUES ( 'Axis_Table'[Values] )
)
)
)
)
Regards,
Xiaoxin Sheng
Thanks,
@v-shex-msft and @mwegener for your support.
Even I have learn new thing out here..!! 🙂
Regards,
Saad
Hi @Anonymous ,
check this out.
Test Measure2 =
CALCULATE (
CALCULATE (
SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
FILTER (
'Calendar',
CONTAINS (
VALUES ( Axis_Table[Values] ),
Axis_Table[Values], SWITCH (
SELECTEDVALUE ( Axis_Table[Type]),
"Year", 'Calendar'[Year_Cal],
"Quarter", 'Calendar'[Quater-Year],
"Month", 'Calendar'[MonthYear_Cal]
)
)
)
)
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |