Forum Discussion

Oscar_Mtz_V's avatar
Oscar_Mtz_V
Icon for Kudo Commander rankKudo Commander
4 years ago
Solved

Distinct count of only the minimum value

This is my data table: Support.pbix I need to have a disctinc count of employees IDs but only count when the instance where they appear is less than or equal to three, and only count the minor ...
Need Help
  • Oscar_Mtz_V's avatar
    Oscar_Mtz_V
    4 years ago

    amitchandak , your response although not the solution, guided me through the right path.
    The key here was the VAR selValue which applied the correct context when used in a Matrix and replaced the SUM for a MIN.

     

    Also, I didn't hard code the value "3", in its place I used a parameter. 

     

    Here is the final DAX that did the trick:

    DCount GUID min with parameter = 
VAR selValue = SELECTEDVALUE('Append'[Standard Value])
VAR Calc= 
    COUNTX(
        FILTER(
            SUMMARIZE(
                ALLSELECTED('Append'),
                'Append'[Employee GUID],
                'Append'[UniqueID],
                "Min", MIN('Append'[Standard Value])
            ),
            [Min] <= [Parameter Value] &&
            [Min] = selValue
        ),
        [Employee GUID]
    )
RETURN
Calc

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Oscar_Mtz_V , Try a measure like

Countx(Filter(summarize(Table, Table[Employee Name], Table[Standard Value], "_1", sum(Table[Value])), [_1] <3), [Employee Name])

  • Oscar_Mtz_V's avatar
    Oscar_Mtz_V
    Icon for Kudo Commander rankKudo Commander
    4 years ago

    amitchandak , your response although not the solution, guided me through the right path.
    The key here was the VAR selValue which applied the correct context when used in a Matrix and replaced the SUM for a MIN.

     

    Also, I didn't hard code the value "3", in its place I used a parameter. 

     

    Here is the final DAX that did the trick:

    DCount GUID min with parameter = 
VAR selValue = SELECTEDVALUE('Append'[Standard Value])
VAR Calc= 
    COUNTX(
        FILTER(
            SUMMARIZE(
                ALLSELECTED('Append'),
                'Append'[Employee GUID],
                'Append'[UniqueID],
                "Min", MIN('Append'[Standard Value])
            ),
            [Min] <= [Parameter Value] &&
            [Min] = selValue
        ),
        [Employee GUID]
    )
RETURN
Calc

     

  • Oscar_Mtz_V's avatar
    Oscar_Mtz_V
    Icon for Kudo Commander rankKudo Commander
    4 years ago

    amitchandak , thanks for your message, the propossed DAX is still not showing the desired results. 

    When I click on "Name 5" on the above table, this should only count the number 1

    Via DAX Studio I looked into the craeted table, and this shoulb ignore the "Name 5" with value 2. 

    I then tried to use the minimun:

    but it is not working either. As when I select "Name 5" the employee keep showin "1" count in both values. 

    There is something in the row context of the visual affecting the result, as in DAX studio, the table iscorrect with the MIN.