Forum Discussion

Water's avatar
Water
Icon for Helper II rankHelper II
5 years ago
Solved

Count based on distinct values in another column of same table – DAX for measure?

Hello, New to Power Bi, so pardon my probably novice (and first!) question. Below is my simplified dataset. I want to create a table counting the number of units worked on in a year as per distinct...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try this measure:

    Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])

    Hope this helps.

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
=
CALCULATE(
    DISTINCTCOUNT( WORKLOAD[WO] ),
    ALLEXCEPT( WORKLOAD, WORKLOAD[YEAR] )
)
  • Water's avatar
    Water
    Icon for Helper II rankHelper II
    5 years ago

    Thank you! Appreciate your time.

     

    But seems not to be working. In the first place I am exactly not sure of your logic, as I see the reference to distinct WO count, but no link to Unit Number count?

     

    Below my measure based on you suggestion:

     

    The output I get is:

    But I was expecting something looking like:

     

     

    Also, when completing the Matrix Visualization with the above Measure 2, it doesn't show "Count of" in the Value field. The Format for Measure 2 is Whole Number.

     

    Just to add: the Unit Numbers are the unique identifiers of each unit being worked on. So Unit 1 was worked on twice in 2019 (with WOs 101 & 103), and twice in 2020 (with WOs 105 & 107). Unit 2 was worked on once in 2019, and twice in 2020.

     

    What would you suggest I try next?

     

    Thanks!