Forum Discussion
Count distinct values AND Filter by Calendar (AND maybe Summarize them)?
Most Valuable ProfessionalHey Nowhitescreens ,
I can answer your first question:
DISTINCTCOUNT: returns the count of distinct values. For example when customer 1111 is buy 3 times a product, DISTINCTCOUNT will return 1 as it counts the distinct values.
COUNTROWS: returns the count of rows. For example when customer 1111 is buy 3 times a product, COUNTROWS will return 3 as the table contains 3 rows.
SUMMARIZE: is a table function. It will return a table that is summarizing the distinct values in the columns you mention as parameter. Here an example from Analytics Insights Ninja:
Check the whole article:
Sorry for the late reply. Got help from a PBI consultant at work.
The solution comes in 3 steps:
1) Create column, distinctcount nr of accounts per person at that time
2) Create column that compare above with all previous dates
3 Create column that specifies "Yes" if nr of accounts have changed per row
Pasting the DAX below. Sorry for not translating it to english, but I trust you get the point.1)
#Antal konton vid transaktionen =var _personnummer = 'TABELL'[PERS_NR]
var _BESLDATUM = TABELL[BESLDATUM]
return
CALCULATE(
DISTINCTCOUNTNOBLANK(
TABELL[KONTONUMMER]),
FILTER(
ALL(
TABELL),
TABELL[PERS_NR] = _personnummer
&& TABELL[BESLDATUM] <= _datum)
)
2)
#Antal konton vid tidigare transaktioner =
var _personnummer = 'TABELL'[PERS_NR]
var _BESLDATUM = TABELL[BESLDATUM]
return
CALCULATE(
DISTINCTCOUNTNOBLANK(
TABELL[KONTONUMMER]),
FILTER(
ALL(
TABELL),
TABELL[PERS_NR] = _personnummer
&& TABELL[BESLDATUM] <= _datum-1)
)
3)
#Nytt konto registrerat under månaden =if(
TABELL[Antal konton vid transaktionen]-TABELL[Antal konton vid tidigare transaktioner]<>0, "Ja", "Nej")
