Visual filtering incorrectly

[o59393]

Hi all

I created a simple calculate dax measure to return me the KPI for launched projects. 

I have 3 type of status:

Launched

On going

On hold

When I filter by launched I get the correct KPI number.

The problem is when I select on goind projects, the dashboard will still show me the KPI for the launched projects, which doesnt make sense.

When I click "on hold" I correctly get an empty table (since there is no time of completion to calculate the metric).

The formula that calculates the KPI for launched projects (for innovation) in this case is:

Total KPI Innovation = 
var _percentage =
(CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Real]),'Seguimiento Proyectos'[Status]="Launched",'Seguimiento Proyectos'[ProjectType]="Innovation")-CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[Status]="Launched",'Seguimiento Proyectos'[ProjectType]="Innovation"))/CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[Status]="Launched",'Seguimiento Proyectos'[ProjectType]="Innovation")

return
IF(_percentage==BLANK(),BLANK(),1-_percentage)

As seen in the dax, it has filtered the word "launched"

When I click on on going I get this:

Does anybody know how I can fix this?

I attach pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhiPixRwl4rd6lFfI?e=yXFt99

Thanks.

[JarroVGIT]

Hi @o59393 ,

A big thank you for attaching a PBIX, that really helps us in helping you!

I had a look and to be honest, I don't understand what is wrong; the measure does exactly what you are telling it to do. Let me walk through what is happening. In you visual, you create a context of the dataset filtered by Quarter, Status and Country. So, if you were to simply sum a column, per row it would sum that column on the dataset corresponding with the quarter, status and country of that row in your visual. 

Now, you are not looking at a simple column but you want to evaluate a measure for each row. You evaluate multiple CALCULATE() statements. CALCULATE statements are very handy because you can further manipulate the context the statement is evaluated against. However, if you are giving a filter on a column that is currently already filtered, you replace the context filter with your own filter. So, when you do this in your measure:

CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Real]),'Seguimiento Proyectos'[Status]="Launched",'Seguimiento Proyectos'[ProjectType]="Innovation")

and that measure is evaluated in a context that is filtered on Quarter, Status and Country, the Status filter is then replaced by the filter you give above. Thus, the calculations will evaluate to the same number, whether you are filtering your visual further on Status or not. 

I don't fully understand what you are trying to achieve but I think you might be helped with using FILTER() statements as you get more control of the exact context the measure is evaluated against (or more accurately, the first argument of the CALCULATE() statement is evaluated against). However, it really depends on what this number needs to represent. It seems you are doing something like (A-B)/A but I don't see what A or B means. Why are you filtering on Status=Launched in your measure calculations? Maybe if you remove that it would already suit your needs, like this:

Total KPI Innovation = 
var _percentage =
(CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Real]),'Seguimiento Proyectos'[ProjectType]="Innovation")-CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[ProjectType]="Innovation"))/CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[ProjectType]="Innovation")

return
IF(_percentage==BLANK(),BLANK(),1-_percentage)

I don't know if that makes any sense, but I do hope my explanation above makes it clear as to why the measure is behaving the way it is 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[o59393]

Hi @JarroVGIT 

The metric basically measures how good are we against the projected gantt time:

The measure is:

(Total days real - total days gantt)/(total days gantt)

Now this has to be considered only for launched projects, since only these have the total days real filled.

If removed the ---- > Seguimiento Proyectos'[Status]="Launched"

from the dax the KPI would consider everything that has total days gantt but no total days real, incurring in a incorrect KPI

The column Innovation 2, has your formula. Which gives me percentages of completion of 200%, which doesnt make sense.

That's why I add the filter "Launched"

Basically I want to know our accuracy with the formula desctibed at the beginning for launched projects, and therefore have it empty for on going & on hold since they dont have real time days.

Hope my explanation is clearer.

I attach again the pbix with your measure. 

https://1drv.ms/u/s!ApgeWwGTKtFdhiXlwMOu_vq_GK57?e=nqtLUv

Thanks

[JarroVGIT]

What I gather from your explanation is that this measure should only return something for Status=Launched.

In your visual, the context in which the measure is evaluated has only 1 value for Status (since we have a column Status, see my previous explanation on how measures and contexts work). So, we can change your measure to this:

Total KPI Innovation = 
var _percentage =
IF(SELECTEDVALUE('Seguimiento Proyectos'[Status]) = "Launched",
(CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Real]),'Seguimiento Proyectos'[ProjectType]="Innovation")-CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[ProjectType]="Innovation"))/CALCULATE(SUM('Seguimiento Proyectos'[Total_Days_Gantt]),'Seguimiento Proyectos'[ProjectType]="Innovation"), BLANK())

return
IF(_percentage==BLANK(),BLANK(),1-_percentage)

This wil only return something if we are looking at Launched projects, not ongoing or on hold projects. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[o59393]

hi @JarroVGIT 

Maybe I should have sent you the final dashboard, my apologies.

The problem is that I will have to select launched (see the arrow) to have shown the graphs (upper left graph):

Otherwise, if you dont have anything selected, I will get something like this

Pretty much I want the dashboard with all the bars when nothing is selected, and when you select launched, the bars update. And when you select on hold/on going the bars dissapear.

Here is the pbix with your solution

https://1drv.ms/u/s!ApgeWwGTKtFdhiawjPyFb7FTN4w6?e=DawubY

Thank you @JarroVGIT  !!!

\

[JarroVGIT · Accepted solution]

Hi @o59393 ,

Here is my PBIX: https://1drv.ms/u/s!Ancq8HFZYL_aiIspsVhGFsqdTHcHCA?e=ztbAe6

What I did was add in a switch statement for Innovation KPI. If I look al all requirements, we need it to calculate when in the current context there is only Launched in status column, (that is the original table issue), but in this dashboard you need to always see launched when nothing is selected. I do feel we are overcomplicating things and would recommend to reconsider your datamodel as a whole at this point, it has become quite complex for not so a complex task/dashboard..

Hope this helps! DOnt forget to give kudo's to show support 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[o59393]

Wow

Very impressive, I still dont know the swtich function very well, seems very usefull.

Would you mind explaining the logic behind? 🙂

return

SWITCH( TRUE(),

HASONEVALUE('Seguimiento Proyectos'[Status]) && SELECTEDVALUE('Seguimiento Proyectos'[Status]) = "Launched", IF(_percentage==BLANK(),BLANK(),1-_percentage),

HASONEVALUE('Seguimiento Proyectos'[Status]), BLANK(),

IF(_percentage==BLANK(),BLANK(),1-_percentage))

One final thing is that I had created a measure to calculate the count of total projects launched, like this:

ID launched = CALCULATE(COUNT('Seguimiento Proyectos'[Id]),'Seguimiento Proyectos'[Status]="Launched")

When I click on on going, I still get a count (should be empty)

how will I get rid of them in that metric called ID launched?

I appreacite a lot your help!!

[o59393]

Hi @JarroVGIT 

I got to solve it with your dax

ID launched = 

var _percentage =

CALCULATE(COUNT('Seguimiento Proyectos'[Id]),'Seguimiento Proyectos'[Status]="Launched")

return
SWITCH( TRUE(),
    HASONEVALUE('Seguimiento Proyectos'[Status]) && SELECTEDVALUE('Seguimiento Proyectos'[Status]) = "Launched", IF(_percentage==BLANK(),BLANK(), _percentage),
    HASONEVALUE('Seguimiento Proyectos'[Status]), BLANK(),
    IF(_percentage==BLANK(),BLANK(), _percentage))

Now the bar chart only shows the launched projects as desired 😄

Thank you so much once again, if you could explain the logic of the switch of this scenario I would appreciate it.

Regards!