Forum Discussion

pasqualino's avatar
pasqualino
Frequent Visitor
1 year ago
Solved

table visual with multiple values

Hello, I finally achieved a decent merge of source files into one table, which looks similar to the following table. Each row represent 1 server. I need to produce a table showing something like the ...
Tips and Tricks
  • m4ni's avatar
    m4ni
    1 year ago

    Hi,

    OK please try the following.

     

    1. Create a date table and join to the data table for filtering.

    2. Add a month column to the date table using the FORMAT function.  Add month as slicer.

    3. Create 1st measure : (For ORACLE ONLY)

    Count Per Day =
    Var Result = CALCULATE(COUNTX(SUMMARIZE(Sheet1, Sheet1[Date], Sheet1[OS]), 1),FILTER(Sheet1, Sheet1[Role] = "oracle"))
    RETURN Result

    4. Create 2nd measure:

    MAX Value = MAXX('date', [Count Per Day])

     

    So using March and Oracle in your example gives me 2 because there are 2 dates with "Oracle" and thats the highest number.  See screenshot.

     

    Hope this helps.  Let me know either way.

pasqualino's avatar
pasqualino
Frequent Visitor
1 year ago

thanks a lot burakkaragoz I think I succeeded with AVG, I am, on the other side, struggling with MAX calculation.

I have added a column for each of the categories I am exploring, setting it to 1 or 0. 

I set measures to 

Max DB2 in a month = MAX(Consulta1[Count DB2]) but it is always showing the same value, not caring about the date filtering ğŸ˜­
v-hjannapu's avatar
v-hjannapu
Community Support
1 year ago

Hi pasqualino,
Thank you burakkaragoz , for your reply regarding query.

Using CALCULATE ensures it respects the filters from your visual, like slicers or matrix rows/columns for month or category. Also, make sure you're using a proper Date field from a calendar table to slice the data by month.
Try updating your measure like this:

Max DB2 in a Month = 
CALCULATE(
    MAX(Consulta1[Count DB2])
)


please find the attached screenshot and pbix file for your reference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team