Forum Discussion

Kerk's avatar
Kerk
Frequent Visitor
4 years ago
Solved

How to enter not aggregated values in a Matrix visual

Hello everyone,   I am using a Matrix to display the minimum AVG-speed achieved grouped by Category and Distance. Following that I would like to bring in the actual date and the actual venue that ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Kerk ,

     

    I suggest you to create measures to calculate Date and Venue for minmum speed.

    Minimum speed = 
CALCULATE (
    MIN ( 'Table'[Speed] ),
    ALLEXCEPT ( 'Table', 'Table'[Category ], 'Table'[Distance] )
)
    Date of Minimum speed = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER ( 'Table', 'Table'[Speed] = [Minimum speed] )
)
    Venue of Minimum speed =
CALCULATE (
    MAX ( 'Table'[Venue] ),
    FILTER ( 'Table', 'Table'[Speed] = [Minimum speed] )
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Kerk's avatar
Kerk
Frequent Visitor
4 years ago

Anonymous thank you very much!

Your reply is very helpful. I followed your example in the attached shenario creating three measures:

  1. PB for Matrix
  2. Date of PB for Matrix
  3. Event for Matrix

I do have correct (non agreegated) results for only three rows though. The rest are returned blank, despite having the full set of data for them. Could you understand why they return no values please?

 

Thank you in advance

kerk

 

file:

https://www.dropbox.com/s/s5wjwnn2wezsxfu/SWM_TestDB.pbix?dl=0 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Kerk ,

 

I find you add two columns in visual level filter to filter your visual. You need to add [Course] and [SwimmerID] into your [PB for Matrix] code.

PB for Matrix = 
CALCULATE (
    MIN ( 'Bookings'[Calc_Time in Sec] ),
    ALLEXCEPT (
        'Bookings',
        Bookings[Styles.Style],
        Bookings[Distance(Distance).Distance],
        Bookings[Course],
        Bookings[SwimmerID]
    )
)

Then you will get correct result. ALLEXCEPT will remove the filter of all columns which are not in it.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Kerk's avatar
    Kerk
    Frequent Visitor
    3 years ago

    Dear Anonymous ,

     

    my data model has slightly changed and I have added two new filters in my visual, so following your example I included these two filters into the ALLEXCEPT. The new PB for Matrix is now:

     

    PB for Matrix = 
CALCULATE (
    MIN ( 'Bookings'[Calc_Time in Sec] ),
    ALLEXCEPT ( 'Bookings', Bookings[Styles.Style],
        Bookings[Distance(Distance).Distance],
        Bookings[Course Type.Course],
        Bookings[Swimmer],
        Bookings[Event Type.EventType], 
        Bookings[Organiser(Organiser).Organiser] )
)

     

    However the results are now wrong, "Date" and "Event" no longer corespond to the "Time".  

    Could you please help me understand what have I done wrong?

     

    https://www.dropbox.com/s/lba01uvfqagr1sn/SWM_TestDB_PBs%20Matrix.pbix?dl=0 

     

    Thank you in advance

    Kerk