Forum Discussion

hajoo125's avatar
hajoo125
Frequent Visitor
7 years ago

Pulling out distinct values based on multiple criteria

Hello Everyone,   Please i need some helping writing some dax function to pull distinct values from a live data feed and based on multiple criteria into a new table. There is a lot of duplicate dat...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi hajoo125 

Create a measure

Measure = CALCULATE(DISTINCTCOUNT('430'[customers]),ALLEXCEPT('430','430'[quarter],'430'[training]))

Add columns and measures in a matrix visual, you could also expand down all level

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • hajoo125's avatar
    hajoo125
    Frequent Visitor
    7 years ago

    Hi Maggie,

     

    Thanks for trying to help out. I still haven't gotten this right, can you give me direct syntax for this? What does '430' in your formula mean? See below a snapshot of my report headers in power bi desktop.

     

     

     

    So i need to extract unique participants id within each quarter on a given set of training modules.

     

    Thematic principale = training module

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi hajoo125 

      "430" is my table name.

      if 'table name'[year],'table name'[quarter] are columns, try measure below

      Measure = CALCULATE(DISTINCTCOUNT('table name'[participants id]),ALLEXCEPT('table name','table name'[year],'table name'[quarter],'table name'[Thematic principale]))

       

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • hajoo125's avatar
        hajoo125
        Frequent Visitor
        7 years ago

        Hi Maggie,

         

        Thanks so much. This works quite well except that i need the function to stretch its scope across all columns because we have two columns that account for beneficiary ids and two columns for training modules. This present function is scanning a single column of each, how can i expand it please? See below for present function and what the headers in the file look like.

         

        Participants = CALCULATE(DISTINCTCOUNT('Sheet1'[Participant ID]),ALLEXCEPT('Sheet1',Sheet1[Year],'Sheet1'[Quarter],'Sheet1'[Thématique Principale]))

         

        Table: