Forum Discussion

dvavrous's avatar
dvavrous
Frequent Visitor
2 years ago
Solved

Count values that equal to Maximum per category.

Hello, 

hope I can get some help. 

I need to count only the values that are equal to maximum value per that category_2. It has to be measure as I want to use multiple slicers. Each time I filter the data by slicer the maximum changes.

Idcategory_1category_2evaluationslicer

1BikesCity Bike0Idea
2BikesCity Bike1Impl
3BikesCity Bike2Impl
4BikesCity Bike2Impl
5BikesCity Bike2Impl
6BikesMountain Bike0Idea
7BikesMountain Bike0Idea
8BikesOther0Idea


I feel I might be close with this measure but it is not quite right. 

count_max =
COUNTX (
    FILTER (
        'Table',
        CALCULATE (
            MAXX (
                SUMMARIZE (
                    ALLSELECTED ( 'Table' ),
                    'Table'[category_1],
                    'Table'[category_2],
                    "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
                ),
                [max_evaluation]
            ),
            ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
        ) = 'Table'[evaluation]
    ),
    COUNT ( 'Table'[category_2] )
)

 

It gets me to this: 
- the issue is with the total for category_1 (Bikes) that does not equal the maximum value for all.
Evaluation02
Bikes(blank)3
  City Bike 3
  Mountain2 
  Other1 
 

Expcted Result

Evaluation02
Bikes33
  City Bike 3
  Mountain 2 
  Other1 



Sample file

https://drive.google.com/file/d/1xZ7gv80k_l90SI6-RNRn83UTA8fhcPir/view?usp=sharinghttps://drive.google.com/file/d/1O0WGiae57tmUzuo077gbMmZ4qFtB847d/view?usp=drive_link

Any help will be appreciated

Need Help
  • dvavrous's avatar
    dvavrous
    2 years ago

    Hello Jianpeng Li, thank you very much for your help.

    It worked on the data I sent but it did not work on my original data. 
    I must have made a mistake in my original measure that I shared.

    Anyway I was able to come up with a different approach that worked.

    For anyone interested see measure below.

    MEASURE =
    VAR _max_table =
    SUMMARIZE (
    ALLSELECTED ( 'Table 2' ),
    'Table 2'[category_1],
    'Table 2'[category_2],
    "max_evaluation", CALCULATE ( MAX ( 'Table 2'[evaluation] ) )
    )
    VAR _values =
    SELECTCOLUMNS (
    'Table 2',
    "category_1", 'Table 2'[category_1],
    "category_2", 'Table 2'[category_2],
    "evaluation", 'Table 2'[evaluation]
    )
    VAR _inter =
    INTERSECT ( _max_table, _values )
    RETURN
    COUNTROWS ( _inter )


3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, dvavrous 

    Based on the PBIX file you provided, I created a new measure using the following DAX expression:

    MEASURE =
VAR _seleted =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[evaluation] ),
        FILTER (
            'Table',
            CALCULATE (
                MAXX (
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[category_1],
                        'Table'[category_2],
                        "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
                    ),
                    [max_evaluation]
                ),
                ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
            ) = 'Table'[evaluation]
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( 'Table'[category_2] ),
        SUMX ( FILTER ( 'Table', 'Table'[evaluation] = _seleted ), [count_max] ),
        COUNTX (
            FILTER (
                'Table',
                CALCULATE (
                    MAXX (
                        SUMMARIZE (
                            ALLSELECTED ( 'Table' ),
                            'Table'[category_1],
                            'Table'[category_2],
                            "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
                        ),
                        [max_evaluation]
                    ),
                    ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
                ) = 'Table'[evaluation]
            ),
            COUNT ( 'Table'[category_2] )
        )
    )

    Here are the results:

     

    I've uploaded the PBIX file I used this time below.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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

    • dvavrous's avatar
      dvavrous
      Frequent Visitor
      2 years ago

      Hello Jianpeng Li, thank you very much for your help.

      It worked on the data I sent but it did not work on my original data. 
      I must have made a mistake in my original measure that I shared.

      Anyway I was able to come up with a different approach that worked.

      For anyone interested see measure below.

      MEASURE =
      VAR _max_table =
      SUMMARIZE (
      ALLSELECTED ( 'Table 2' ),
      'Table 2'[category_1],
      'Table 2'[category_2],
      "max_evaluation", CALCULATE ( MAX ( 'Table 2'[evaluation] ) )
      )
      VAR _values =
      SELECTCOLUMNS (
      'Table 2',
      "category_1", 'Table 2'[category_1],
      "category_2", 'Table 2'[category_2],
      "evaluation", 'Table 2'[evaluation]
      )
      VAR _inter =
      INTERSECT ( _max_table, _values )
      RETURN
      COUNTROWS ( _inter )


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, dvavrous 
        Thank you very much for your reply. Did the DAX expression you shared solve your current problem?

         

        Best Regards

        Jianpeng Li