cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
campelliann
Resolver I
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

 

MeasureMeasureFilter by the team. The totals exceed the sum of each rowFilter by the team. The totals exceed the sum of each row

1 ACCEPTED SOLUTION
campelliann
Resolver I
Resolver I

The solution was:
 
MEASURE Alocacaodiaria2 =
VAR Virtual_table =
ADDCOLUMNS (
'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])


View solution in original post

4 REPLIES 4
campelliann
Resolver I
Resolver I

The solution was:
 
MEASURE Alocacaodiaria2 =
VAR Virtual_table =
ADDCOLUMNS (
'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])


v-shex-msft
Community Support
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.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ValtteriN
Super User
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:

ValtteriN_0-1643460769814.png


Dax:

FailingTotal = SUM(FailingTotal[Value2])*SUM(FailingTotal[Value])
WorkingTotal = IF(ISBLANK(SELECTEDVALUE(FailingTotal[dimension2])),
SUMX(FailingTotal,FailingTotal[Value]*FailingTotal[Value2]),
FailingTotal[FailingTotal])
 
End result:
ValtteriN_1-1643460863169.png

 



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!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





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

Proud to be a Super User!




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...

 

Picture1.png

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?

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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