cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Countax with selectedvalue expresion returns Zero

Hi

I started with this thread wondering how to solve the issue. From there I got to a solution with a DAX meassure but it's not working for the totals.

I got this table:

ActorTime StartTime EndDateTotal hoursContent typeID
A10:0012:0001/01/20212.00AA01/01/202110:0012:00
B08:0012:0002/01/20214.00BB02/01/20218:0012:00
A10:0012:0003/01/20212.00AA03/01/202110:0012:00
A12:0014:0003/01/20212.00ABAB03/01/202112:0014:00
A10:0012:0004/01/20212.00ABAB04/01/202110:0012:00
B10:0012:0004/01/20212.00ABAB04/01/202110:0012:00
A09:0013:0005/01/20214.00ABAB05/01/20219:0013:00
B16:0018:0005/01/20212.00ABAB05/01/202116:0018:00

 

 

It records whos is recording in a studio (Actor) and what is recording (content type).

They can record content only for them (A or B) or content for both (AB).

The content for both (AB) can be recorded separately or together (same time and date)

 

The problem is when they record together, the recording session adds the double of time. i.e. the session of the 04/01/2021 A and B are recording together in a 2 hours session length, but the sum will acount it as 4 hours of recording.

 

My approach is concatenat the ID column = [Content type] & [Date] & [time start] & [time end]

This ID will be repeated by the people recording togehter (same time and date), therefore I wrote this Measure:

 

both adjst = SUMX ( Data, Data[Total hours] / COUNTAX ( Data, SELECTEDVALUE ( Data[ID] ) ) ) 

 

It works as expected

Capture.PNG

Just the date 04/01/2021 is summirezed and divided by the count of IDs... the problem is that I need the total as well.

 

I understand the the total returns infinity because theres no selectedvalue and returns Zero, so the division is infinity, but I don't know other way to do this. It could work as well in a DAX added column but thhis way selectedvalue won't work.

 

I tried using and IF:

 

both adjst 2 =
IF (
    ISBLANK ( SELECTEDVALUE ( Data[ID] ) ),
    SUMX ( Data, Data[Total hours] / COUNTA ( Data[ID] ) ),
    SUMX ( Data, Data[Total hours] / COUNTAX ( Data, SELECTEDVALUE ( Data[ID] ) ) )
)

 

 

but this doesn't work for the total

Capture.PNG

 

I tried CALCULATE and the expresion above with an filtering thinking it would be executed row by row...but the result is the same.

 

I'm thinking a dax column would do the work but no clue how to do it. A new column with the fixed values for Total hours, meaning Total hours divided by the count of The ID appear in the table.

 

Any idea will be welcome 🙂

thanks

 

Pepe

 

 

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User II
Super User II

Hello @Anonymous 
Create a calculated column using the below calculation.

Hour =
VAR TotalID =
CALCULATE ( COUNT ( Recordings[ID] ), ALLEXCEPT ( Recordings, Recordings[ID] ) )
RETURN
DIVIDE ( [TotalHours], TotalID )

Capture.PNG

View solution in original post

9 REPLIES 9
wdx223_Daniel
Super User II
Super User II

Anonymous
Not applicable

Ohhh that was something I though about but didn't try how to do it. I'm gonna implement that in my report but I think that's going to work 🙂

Thanks!!

 

Anonymous
Not applicable

It works well a solves the problem!!!

Cheers!!

TarunSharma
Resolver III
Resolver III

Hello @Anonymous 
As you've mentioned the A and B are recording so the hours will the sum (4 hours), but in the count of ID = 2, I can see only 2 hours.
Are you looking for something like the below image?
Capture1.PNG

Anonymous
Not applicable

Hi @TarunSharma 

 

No, the total is not 20, it should be 18. Because the hours at 4/01/21 are 2 not 4.

My measure is working at a row level, but not for the total that returns infinity, thats my problem.

 

Thanks!!

Hello @Anonymous 
Instead of measure try a calculated column and plot into the table.

Anonymous
Not applicable

Yep I think that a good one, but I don't know how to do that calculation in a column. 😅

Hello @Anonymous 
Create a calculated column using the below calculation.

Hour =
VAR TotalID =
CALCULATE ( COUNT ( Recordings[ID] ), ALLEXCEPT ( Recordings, Recordings[ID] ) )
RETURN
DIVIDE ( [TotalHours], TotalID )

Capture.PNG

View solution in original post

Anonymous
Not applicable

Wow That's it!!! Modify the time in a new column allows me to calculate speed metrics by actor and conten type, not just by content type 😄

Amazing!!!

 

Thanks a lot for your help!

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors