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 the number of adnumbers with two titles

 

And then to do something like

 

something like  

calculate(count(adnumber),count(title name)=2)

 

so 001 = 2 , 002=1

 

Thanks

 

Chris

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.

8 Replies

  • 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.