Forum Discussion

Guenfood77's avatar
Guenfood77
Frequent Visitor
2 years ago
Solved

DAX Count values when superior to other value

Hi,   I'd like to display a graph, by date, to show count of values with these conditions   CALCULATE( COUNTA('SNOW'[Number]), 'SNOW'[URL] IN { "KB123" } ) > CALCULATE( COUNTA('SNOW'[Number])...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Guenfood77 ,

     

    I'm reviewing the posts and I found out that the fields you marked in yellow are measures. Here I would like to add that the measures are dynamic and the calculated columns are static. As a result, we don't usually reference measures into calculated columns. 

    • If you reference a measure in a calculated column, it will lead to an error. Measures are dynamic and context-dependent, whereas calculated columns are static. Mixing them can cause inconsistencies.
    • Bad Result: The calculated column won’t behave as expected, and you might encounter unexpected issues.

    Instead, we can use a static calculated column as a reference and call it in a measure.

     

    Best Regards,

    Stephen Tao

     

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

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

 

First, you'll create two measures to calculate the daily counts for each condition (URL = "KB123" and Creator = "HELP DESK_L1").

 

Count_KB123 = 
CALCULATE(
    COUNTA('SNOW'[Number]),
    'SNOW'[URL] = "KB123"
)
Count_HELP_DESK_L1 = 
CALCULATE(
    COUNTA('SNOW'[Number]),
    'SNOW'[Creator] = "HELP DESK_L1"
)

 

 

Next, you'll create a measure that compares these two counts on a day-by-day basis. This measure will return the count for KB123 incidents if it's greater than the count for HELP DESK_L1 incidents, otherwise, it could return 0 or another placeholder value to indicate that the condition is not met.

 

Count_Comparison = 
IF(
    [Count_KB123] > [Count_HELP_DESK_L1],
    [Count_KB123],
    BLANK() // or 0, depending on how you want to handle days where the condition is not met
)

 

 

In your visual : 

  • Date Axis: Use the date field from your SNOW table (or a related Date table if you have a Date dimension in your model) as the axis for your graph.
  • Value: Use the Count_Comparison measure to display the count of KB123 incidents only for days where it exceeds the count of HELP DESK_L1 incidents.
Guenfood77's avatar
Guenfood77
Frequent Visitor
2 years ago

Thanks for your answer.

It seems formula is OK, but i realize that one condition is not define.

I forgot to add a filter by impacted user. I must display the result if it concern a same user and not for a glocal count.

Example : If [Count_KB123] > [Count_HELP_DESK_L1] for a same [USER_ID]...

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    2 years ago

    In that case I think you need to create a calculated column first : 

    IsKB123Greater = IF(YourTableName[Count_KB123] > YourTableName[Count_HELP_DESK_L1], 1, 0)

    Then :

    Top Employee by Client and Condition = 
SUMMARIZE(
    FILTER(
        YourTableName,
        YourTableName[IsKB123Greater] = 1
    ),
    YourTableName[Client],
    "Top Employee", CALCULATE(MAXX(
        FILTER(
            YourTableName,
            YourTableName[Interactions] = MAX(YourTableName[Interactions])
        ),
        YourTableName[Employee]
    )),
    "Max Interactions", MAX(YourTableName[Interactions])
)
    • Guenfood77's avatar
      Guenfood77
      Frequent Visitor
      2 years ago

      Thanks for your help. But i'm lost with fields (Client, Interactions...)you've mentioned from

      YourTableName[Client],
    "Top Employee", CALCULATE(MAXX(
        FILTER(
            YourTableName,
            YourTableName[Interactions] = MAX(YourTableName[Interactions])
        ),
        YourTableName[Employee]
    )),
    "Max Interactions", MAX(YourTableName[Interactions])

       I suppose that Employee match to User_Id in my case...

       

      Another question : I create the calculate column and i need to create another one with other values (just changing the 2 table names) but it doen't work. Formula doen't register...

      • AmiraBedh's avatar
        AmiraBedh
        Icon for Super User rankSuper User
        2 years ago

        Can you please share your pbix file, the structure of the table ?