DAX - Help

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Greg_Deckler]

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

[Anonymous]

Hi @Greg_Deckler,

Thanks for getting back..

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

Thanks

Chris

[Greg_Deckler]

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]

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]

I'd need sample data to replicate.

[Anonymous]

 @Greg_Deckler

Adnumber  Title Name Date

001               Bob              01/05/2017

001               Fred             01/06/2017

002               Bob              01/02/2016

So it would return where year is 2017

Number of Ads     Number of Titles

1                             2

Thanks 

[Ashish_Mathur · Accepted solution]

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.

[Anonymous · Accepted solution]

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