Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Support with DAX Function NOT Working

Hi Community,

 

Need your! Can anyone tell me why when I do not have a selection on the two VAR below 'aaa' and 'bbb', my chart is blank? 

Can I get anyone's support to adjust the function in order to have the bit "VAR SUM_ORC = CALCULATE(SUM(t_Incorridas_SGC[Incorridas_Totais_Recurso]))" working, even if there are no selections at all? 

 

Thank you so much! 

 

 

Effort Hrs. =
VAR bbb = CALCULATE(SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Ref_GIP]))
VAR aaa = CALCULATE(MAX(t_Detalhe_Processos_Fases_Tarefas[Data_Info]);
FILTER(t_Detalhe_Processos_Fases_Tarefas; t_Detalhe_Processos_Fases_Tarefas[Ref_GIP] = bbb)
)
VAR SUM_ORC = CALCULATE(SUM(t_Incorridas_SGC[Incorridas_Totais_Recurso]);
FILTER(t_Detalhe_Processos_Fases_Tarefas;t_Detalhe_Processos_Fases_Tarefas[Ref_GIP] = bbb);
FILTER(t_Detalhe_Processos_Fases_Tarefas;t_Detalhe_Processos_Fases_Tarefas[Data_Info] = aaa))
RETURN

IF(SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Data_Info]) = aaa
|| SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Ref_GIP]) = bbb;
CALCULATE(SUM_ORC); CALCULATE(SUM_ORC))
1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@Anonymous  Guess, you are looking for a Measure something like this... Show the relevant sum while there is a selection and if there is no selection then show total sum.

 

Test232 = 
VAR _SelectedSUM = CALCULATE(SUM(emp[sal]),FILTER(emp,emp[deptno] = SELECTEDVALUE(emp[deptno])))
VAR _TotalSUM = SUM(emp[sal])
RETURN IF(ISBLANK(_SelectedSUM),_TotalSUM,_SelectedSUM)

image.pngimage.pngimage.pngimage.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

8 REPLIES 8
PattemManohar
Community Champion
Community Champion

@Anonymous  Guess, you are looking for a Measure something like this... Show the relevant sum while there is a selection and if there is no selection then show total sum.

 

Test232 = 
VAR _SelectedSUM = CALCULATE(SUM(emp[sal]),FILTER(emp,emp[deptno] = SELECTEDVALUE(emp[deptno])))
VAR _TotalSUM = SUM(emp[sal])
RETURN IF(ISBLANK(_SelectedSUM),_TotalSUM,_SelectedSUM)

image.pngimage.pngimage.pngimage.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi there, 

 

Actually I found a small issue with the behaviour that it turned out. 

With your logic applied, I was able to retrieve data even if no selections were made, however, when making a selection by month, it will not give values for that particular month, instead it retrieves the sum of it. 

 

TEST = 

VAR _SelectedSUM = CALCULATE(SUM(t_Incorridas_SGC[Incorridas_Totais_Recurso]);
FILTER(t_Detalhe_Processos_Fases_Tarefas;t_Detalhe_Processos_Fases_Tarefas[Data_Info] = SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Data_Info]));FILTER(t_Detalhe_Processos_Fases_Tarefas; t_Detalhe_Processos_Fases_Tarefas[Ref_GIP] = SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Ref_GIP]))
)

VAR _TotalSUM = SUM(t_Incorridas_SGC[Incorridas_Totais_Recurso])

RETURN IF(ISBLANK(_SelectedSUM);_TotalSUM;_SelectedSUM)
Anonymous
Not applicable

Can you support, please @PattemManohar ?

@Anonymous Try changing your measure logic as below, (Changed the filter criteria to filter based on any fields selection instead of specific fields)

 

Test232 = 
VAR _SelectedSUM = CALCULATE(SUM(emp[sal]),ALLSELECTED(emp))
VAR _TotalSUM = SUM(emp[sal])
RETURN IF(ISBLANK(_SelectedSUM),_TotalSUM,_SelectedSUM)

It's always worth sharing some sample data and expected output to understand the scenario in detail.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Still not what I want to achieve. 

I have adjusted my function to this: 

TEST = 

VAR bbb = CALCULATE(SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Ref_GIP]))
VAR aaa = CALCULATE(MAX(t_Detalhe_Processos_Fases_Tarefas[Data_Info]);
ALLSELECTED(t_Detalhe_Processos_Fases_Tarefas))

VAR SUM_ORC = CALCULATE(SELECTEDVALUE(t_Incorridas_SGC[Incorridas_Totais_Recurso]);
ALLSELECTED(t_Detalhe_Processos_Fases_Tarefas)
)

VAR SUM_Custo = CALCULATE(SELECTEDVALUE(t_Incorridas_SGC[Custo_Calc]);
ALLSELECTED(t_Detalhe_Processos_Fases_Tarefas)
)
RETURN

IF(SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Data_Info]) = aaa;
CALCULATE(SUM_ORC * SUM_Custo)
)
 
Problem is that when I have no selections I get the whole dataset calculated multiple times over the months whereas I should only have totals by month! 
Can you please help me ?
 
Light Blue = Hours Worked
Dark Blue = Cost 
I have set these equally, so 1 hour = 1 cost
and as you can see for Hours works well and for Cost it sums up to whatever values. I am not getting there. THANKS! 
1.JPG
Anonymous
Not applicable

Smooth! 

Thanks a lot for your help. 

yelsherif
Resolver IV
Resolver IV

 so theThe first VAR seems to be incorrect
VAR bbb = CALCULATE(SELECTEDVALUE(t_Detalhe_Processos_Fases_Tarefas[Ref_GIP]))

It will always return blank if nothing is selected in t_Detalhe_Processos_Fases_Tarefas[Ref_GIP] and so the filter will return a blank set of rows, resulting in a blank chart

I think you need to change the logic of VAR bbb

Anonymous
Not applicable

Thank you! 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.