Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX - Help

Hi,   Have a table with the following format     Adnumber  Title Name 001               Bob 001               Fred 002               Bob   I want to be able to have a measure which counts t...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    You can try to use below formula to get the different count of year group.

    Summary table =
SUMMARIZE (
    ADDCOLUMNS ( Table, "Year", YEAR ( table[Date] ) ),
    [Year],
    "Count Titile", DISTINCTCOUNT ( Adnumber[Title] ),
    "Count AD", DISTINCTCOUNT ( Adnumber[Adnumber] )
)

     

    Regards,

    Xiaoxin Sheng

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi Anonymous,

     

    Create a calendar table and extract year from the data column of the calendar table using the YEAR() function.  Create a relationship from the Date column of your data table to the date column of your calendar table.  Drag year from the calendar table to the visual or as a silcer or to the filter section.  Select a particular year.  Now use the IDSTINCTCOUNT() function.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago
Table 4 = SUMMARIZE(Adnumber,Adnumber[Adnumber],"Number",DISTINCTCOUNT(Adnumber[Title]))
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Greg_Deckler,

 

Thanks for getting back..

 

Thats brilliant... How would I add a where clause into that?

 

Thanks

 

Chris

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Would depend on where you want the where clause but you could do something like:

     

    Table 4 = SUMMARIZE(Adnumber,Adnumber[Adnumber],"Number",CALCULATE(DISTINCTCOUNT(Adnumber[Title]),FILTER(Table,Criteria)))

    Basically, wrap your DISTINCTCOUNT in a CALCULATE and add a FILTER clause.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Greg_Deckler,

       

      I got it to work ok

       

      Table 4 = SUMMARIZE(Query1,Query1[adnumbr],"Number",CALCULATE(DISTINCTCOUNT(Query1[ttlcode]),Query1[ttlcode]="ENS" || Query1[ttlcode]="CN" || Query1[ttlcode]="WN" || Query1[ttlcode]="TS" || Query1[ttlcode]="HC" || Query1[ttlcode]="NWEM", YEAR(Query1[dateins] = 2017)))

      but the year part doesnt seem to do anything?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        I'd need sample data to replicate.