cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsch001
Frequent Visitor

DAX formula help - raw data available with intended result set

The raw data is a combination of 3 dimensions (User, Calendar which are dates with a start of week computed, and ExamType) and 1 fact table with the scoring information.

RawData:   
     
UserDateWeekOfExamScore
Tim1/2/201912/31/2018Baseline75
Ellie1/3/201912/31/2018Baseline90
Jon1/2/201912/31/2018Baseline88
Ellie1/10/20191/7/2019Update91
Tim1/10/20191/7/2019Update90
Ellie1/21/20191/21/2019Update95
Sophia1/21/20191/21/2019Baseline63
Sophia1/29/20191/28/2019Update77

 

 

Below is what I'm hoping for in a result set from the DAX formula, the AverageScore can be a measure created later... I'm looking for Scores and Users by week. Each User will take a "Baseline" ExamType and get a score, after they get their intial baseline score, they can update it by taking an "Update" ExamType and changing their score. I care the latest ExamType for each user within the Week context. 

 

Intended DAX Result Set:  
    
Week OfScores (numerator)Users (denominator)AverageScore
12/31/2018253384.33333333
1/7/2019269389.66666667
1/14/2019269389.66666667
1/21/2019336484
1/28/2019350487.5

 

Description of what the result set is showing us:

 

Week OfDescription of Activity
12/31/2018Tim, Ellie, Jon take baseline exam (we sum their scores and divide by the 3 users to get average)
1/7/2019Tim and Ellie take update exam and change score from baseline (we sum Tim/Ellies Update and Jons baseline, divide by 3)
1/14/2019no activity, same as prior week
1/21/2019Ellie takes update exam and changes scores, Sophia takes baseline exam, User count increases (we take Ellies latest Update exam score, Tims Update score, Jon and Sophias baseline exam scores because they don't have an update available)
1/28/2019Sophia takes update exam and changes score, user count stays

 

 

Any help is greatly appreciated, this has been a brain buster for me for awhile!

2 REPLIES 2
Super User IV
Super User IV

Re: DAX formula help - raw data available with intended result set

Hi @bsch001 

 

Please find the PBIX.


Based on your source data I have created the Intended DAX results.

 

image.png

 

What I did was to create the 3 measures as detailed in the PBIX.

 

I then used the column called WeekOf to get it showing the values per week.

 

If there are any additional questions please let me know.

 





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

Proud to be a Super User!






Power BI Blog
Super User I
Super User I

Re: DAX formula help - raw data available with intended result set

@bsch001I think that I have what you may need.

I was succesful in feeding your logic to come up with a measure that makes the appropriate numbers of rows (all rows with WeekOf <= curent filter context WeekOf date) visible to the current filter context (WeekOf) for the calculation

 

Capture.PNG

Numerator :=
VAR _1 =
    MAX ( 'Table'[WeekOf] )
VAR _3 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ),
        [User],
        "X", MAX ( [WeekOf] ),
        "Y", MAX ( [Score] )
    )
VAR _5 =
    SUMX ( _3, [Y] )
RETURN
    _5

Denominator := 
VAR _1 =
    MAX ( 'Table'[WeekOf] )
VAR _3 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ),
        [User],
        "X", MAX ( [WeekOf] ),
        "Y", MAX ( [Score] )
    )
VAR _5 =
    COUNTX( _3, [Y] )
RETURN
    _5

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors