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
campelliann
Post Patron
Post Patron

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
Post Patron
Post Patron

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
Post Patron
Post Patron

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

Top Solution Authors