DAX Count values when superior to other value

[Guenfood77]

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.

[AmiraBedh]

 

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]

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]

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]

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]

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

[Guenfood77]

Here is the structure table. Id d'utilisateur_1 is my ID_User

Greater DW = IF(SNOW[Nombre de Numéro pour KB0022992] > SNOW[Nombre de numéro pour L1], 1, 0)

Greater PFAI must be : IF(SNOW[Nombre de Numéro pour KB0022979] > SNOW[Nombre de numéro pour BNPP_BCEF-IT_PFAI_], 1, 0)

[v-stephen-msft · Accepted solution]

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.