Forum Discussion

VimWan's avatar
VimWan
Frequent Visitor
3 years ago

How to create a matrix with values that are less than or equal to the column value

Hi Everyone,

 

I'm quite a novice with Power Bi and am having an issue figuring out an efficient way to complete the following:

 

I have 2 tables, one that contains Student Enrollment data and a table I've created which contains the reporting dates of the report I want populated. I've createad an example below:

 

Student enrollment data:

stdnt #programadd datedrop date
1accounting1/20/2022 
2human resources3/15/2022 
3accounting2/22/20222/25/2022

 

Report dates:

report dates
1/25/2022
3/16/2022
4/25/2022

 

What I want in Power Bi, is for my table to look like the attachment below, where the report dates are the column headers and the counts of all enrollment records for each program that have an Add date that is less than or equal to the report date for each program is listed in the body of the table where the values would go.

 

 

Thanks for your time and help with this.

 

General Comment
Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    that seems like a straightforward crossjoin.  What have you tried and where are you stuck?

    • VimWan's avatar
      VimWan
      Frequent Visitor
      3 years ago

      Thanks Ibendin,

      I understand that a crossjoin mashes all the rows from both table together, is there a way for us to specify that we want to mash specific records from the student enrollment data to every consecutive date record where the add date of the student enrollment table record must be less than or equal to the report date? 

      • VimWan's avatar
        VimWan
        Frequent Visitor
        3 years ago

        Right now from your suggestion, I'm thinking I can crossjoin all rows from both tables and then remove any rows where the Add Date > Report Date after the join. Since this seems inefficient, any suggestions to improve this?

        Another point I wanted to make is that if my student enrollment table is a table with a large number of rows, wouldn't using a cross join potentially cause performance issues?  

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    3 years ago

    Do you want a YTD count of enrollment at the specified report dates? If yes, you can achieve this by setting up a date table and YTD measure. Even if the below YTD calculation isn't exactly what you want, I suspect the general approach may still work for you even if you have to modify the measure in #3 to get exactly what  you want.

     

    Details. Given tables names 'StudentEnrollment' and 'ReportDates' for your data provided:

    1) Create date table. There are lots of robust methods, but here is some very quick DAX just to get the visual working:

     

    Dates = 
VAR minDt = DATE( YEAR( MIN( MIN( ReportDates[report dates] ), MIN( StudentEnrollment[add date] ) ) ), 1, 1 )
VAR maxDt = DATE( YEAR( MAX( MAX( ReportDates[report dates] ), MAX( StudentEnrollment[add date] ) ) ), 12, 31 )
RETURN
CALENDAR( minDt, maxDt )

     

    2) Set up relationships between your two tables and Dates ( StudentEnrollment <--M:1-- Dates --1:M--> ReportDates):

    3) Create the measure you want for counting enrollment:

     

    EnrollmentCount = 
CALCULATE( 
    COUNTROWS( StudentEnrollment ), 
    CALCULATETABLE( DATESYTD( Dates[Date] ), ReportDates ) 
)

     

    4) Construct the visual you want - from your screenshot, it look like you want a matrix: