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]),
	'SNOW'[Creator] IN { "HELP DESK_L1" })

 

I need to display number of incidents per day when nb incidents where url is KB123 are greater than nb incidents where creator is HELP DESK_L1.

 

Thanks for your help.

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.           

7 Replies

  • 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])
)