Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Guenfood77
Frequent Visitor

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.

1 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.           

View solution in original post

7 REPLIES 7
AmiraBedh
Super User
Super User

 

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.

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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]...

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

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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...

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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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)

Structure_table.jpg

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.           

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.