cancel
Showing results for
Did you mean:
Resolver I

## Help me Dax Geniuses - Images from the Measure/Visuals included

Hi people,

So I basically have the Beginning and Ending Date for my projects. And I need, as a part of a bigger challenge, to intersect the number of days when the project was active with the current dates filter provided by the user. My measure works on the level of the project, but completely fails in the totals (which should be the sum of every project active days). I believe in the totals the measure considering an intersection of 365 days for every project or something like that ...

For example: Project 1: 5 days active/2020 and 5 days active 2021 //// Project 2 10 days active/2020, 10 days active 2021.

The result shouls be 15 days total /2020 and 15 days 2021

PS: the measure refers to 2 tables (Calendar and Project) that are not related in the model

MeasureFilter by the team. The totals exceed the sum of each row

1 ACCEPTED SOLUTION
Resolver I
The solution was:

MEASURE Alocacaodiaria2 =
VAR Virtual_table =
'Triskell Completo',
"@Total days",
CALCULATE (
CALCULATE (
COUNTROWS ( 'Calendario Ano (Automatico)' ),
'Calendario Ano (Automatico)'[Date]
>= SELECTEDVALUE ( 'Triskell Completo'[Projecções Data Início] ) //calendar date greater than beginning
&& SELECTEDVALUE ( 'Triskell Completo'[Produção] ) >= 'Calendario Ano (Automatico)'[Date] // calendar date less than End
&& 'Calendario Ano (Automatico)'[Date]
IN VALUES ( 'Calendario Ano (Automatico)'[Date] )
)
)
)

RETURN
sumx(virtual_table,'Triskell Completo'[Alocação diária]*[@Total days])

4 REPLIES 4
Resolver I
The solution was:

MEASURE Alocacaodiaria2 =
VAR Virtual_table =
'Triskell Completo',
"@Total days",
CALCULATE (
CALCULATE (
COUNTROWS ( 'Calendario Ano (Automatico)' ),
'Calendario Ano (Automatico)'[Date]
>= SELECTEDVALUE ( 'Triskell Completo'[Projecções Data Início] ) //calendar date greater than beginning
&& SELECTEDVALUE ( 'Triskell Completo'[Produção] ) >= 'Calendario Ano (Automatico)'[Date] // calendar date less than End
&& 'Calendario Ano (Automatico)'[Date]
IN VALUES ( 'Calendario Ano (Automatico)'[Date] )
)
)
)

RETURN
sumx(virtual_table,'Triskell Completo'[Alocação diária]*[@Total days])

Community Support

Hi @campelliann,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

Hi,

Totals are often an issue, since they use logic that isn't always intuitive. I recommend cheking this article by SQLBI to understand why the value you got doesn't make sense: https://www.sqlbi.com/articles/summing-values-for-the-total/

One easy solution to solve issues with totals is to use IF with SELECTEDVALUE to change the calculation logic for the total row. Example:

Data:

Dax:

FailingTotal = SUM(FailingTotal[Value2])*SUM(FailingTotal[Value])
WorkingTotal = IF(ISBLANK(SELECTEDVALUE(FailingTotal[dimension2])),
SUMX(FailingTotal,FailingTotal[Value]*FailingTotal[Value2]),
FailingTotal[FailingTotal])

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Resolver I

Here is my latest attempt with summarize.

I remind that the goal here is to intersect the days in which each project was active and the current dates filter context.

Example: Project 1 occured from 1 january to 15 february. The output is 31 days worked in January and 15 in February.
Than I want to sum all the days worked for each month...

It seems that in the totals, it ignores the dates from the projects. When the project is as filter context it works. So I tried to build a new global summarized virtual table, still it does not work, why?

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors