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
I have some issues with my dax code because it doesn't sum the total, and after thinking it was because of a ISINSCOPE function, I get that the Switch I have inside doesn't sum either and I can't find how to fix it.
This is my code:
Total_Estimated_Resources =
IF(
ISINSCOPE('Dim_Project Database'[Program Name]) ,
SWITCH(
SELECTEDVALUE('Dim_Project Database'[Program Name]),
"Non billable" , NonBillingResourceCost,
"Internal Project", PendingResources - NonBillingResourceCost,
BLANK()
)
+
IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())
,BLANK()
)
Solved! Go to Solution.
I resolved the issues finally.
First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.
After, I separated a little the code proposed by @amitchandak (thank you!) to understand it better and here is the code:
VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])
VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
[Program Name],
"Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
"Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),
BLANK()
)
)
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))
RETURN Total_Cost
I resolved the issues finally.
First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.
After, I separated a little the code proposed by @amitchandak (thank you!) to understand it better and here is the code:
VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])
VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
[Program Name],
"Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
"Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),
BLANK()
)
)
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))
RETURN Total_Cost
@MacarenaGB , Thanks for posting the solution. Kudos to you....
I resolved the issues finally.
First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.
After, I separated a little the code proposed by @amitchandak (thank you!) to understand it better and here is the code:
VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])
VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
[Program Name],
"Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
"Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),
BLANK()
)
)
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))
RETURN Total_Cost
Hi! @amitchandak
Something better but weird:
It is muliplying by 10 the values.
@MacarenaGB , _1 is a new column in summarize. [_1], when used same in sumx .
As I can + sign near to program name. I think you need to add more columns to summarize add all columns on the row in that.
Hi! @amitchandak
I'm trying to understand it, but not yet 😞
You are saying that, with the SUMX I will get the correct total and I need to create a virtual table with the proper columns to sum them correctly, right?
In my table, I have Year and Month, but even if remove them of the matrix, the result of the column don't change.
@MacarenaGB , Try like
Total_Estimated_Resources =
Sumx(summarize('Dim_Project Database', 'Dim_Project Database'[Program Name],'Dim_Project Database'[Project Type], "_1", IF(
ISINSCOPE('Dim_Project Database'[Program Name]) ,
SWITCH(
SELECTEDVALUE('Dim_Project Database'[Program Name]),
"Non billable" , NonBillingResourceCost,
"Internal Project", PendingResources - NonBillingResourceCost,
BLANK()
)
+
IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())
,BLANK()
)),[_1])
refer: https://www.youtube.com/watch?v=ufHOOLdi_jk
@MacarenaGB , try like
Total_Estimated_Resources =
Sumx(summarize('Dim_Project Database', 'Dim_Project Database'[Program Name],'Dim_Project Database'[Project Type],'Dim_Project Database'[Year],'Dim_Project Database'[Month], "_1", IF(
ISINSCOPE('Dim_Project Database'[Program Name]) ,
SWITCH(
SELECTEDVALUE('Dim_Project Database'[Program Name]),
"Non billable" , NonBillingResourceCost,
"Internal Project", PendingResources - NonBillingResourceCost,
BLANK()
)
+
IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())
,BLANK()
)),[_1])
Hi!
I tried that before but I can't access to the Calendar columns (it is other table)
Hi, @MacarenaGB
I think you can change the columns that you circled in the DAX formula to
'Dim_Project Database'[Year],'Dim_Project Database'[Month]
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 August 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
77 | |
71 | |
47 | |
39 |
User | Count |
---|---|
136 | |
108 | |
70 | |
64 | |
57 |