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.

m4ni's avatar
m4ni
Resolver I
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

    Could you please help me with the date table and join? I already have date on each data table row, is it a must to have a separate table?

    • m4ni's avatar
      m4ni
      Resolver I
      1 year ago

      The date table is a must, yes. Firstly its best practice and secondly, in your example using the data table gives wrong results (I tested that), because you dont have unique values.

      However if you want to know the workaround, please amend the second measure to:

       

      MAX Value = MAXX(VALUES(Sheet1[Date]), [Count Per Day])
       
      That will give you correct numbers.  Assuming the numbers are correct of course.
       
      The date table can be created using the CALENDARAUTO function in DAX.
      1. New table > CALENDARAUTO()
      2. Add date columns as needed.
  • pasqualino's avatar
    pasqualino
    Frequent Visitor
    1 year ago

    Thanks a lot m4ni I succeeded in creating date table, I am now struggling with the Daily count as it is returning the same value if I filter for Oracle, Sql or any other value I have in my tables 😕

    • m4ni's avatar
      m4ni
      Resolver I
      1 year ago

      Hi pasqualino 

      The reason for incorrect values is likely because you dont have a unique key in your table therefore the records are getting grouped.  Please try these steps:

       

      1. Go into Power Query and add an Index column from 1

      2. Amend the Count Per Day measure to now summarize by Index rather than OS.

       

      This should give you correct results as you now have uniqueness due to Index.

       

      Please confirm how you wish to display the results.  If you want to display the max value in a seperate card visual then you need to filter Count Per Day measure for either "sql", "oracle" or other values.

      If you want to display the max value in a table then you can take out the filter on role.

      Screenshot should help make sense...