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

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 ?

    • Guenfood77's avatar
      Guenfood77
      Frequent Visitor
      2 years ago

      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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.