Count distinct values AND Filter by Calendar (AND maybe Summarize them)?

Question

Sorry for the prntScrn pics, but I cant paste with ctrl+V (worked fine earlier this week).&nbsp;&nbsp;&nbsp;

selimovd · Answer

Hey&nbsp;Nowhitescreens&nbsp;,&nbsp;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.&nbsp;COUNTROWS:&nbsp;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.&nbsp;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&nbsp;Analytics Insights Ninja:&nbsp;Check the whole article:&nbsp;Creating Summary Tables Using DAX SUMMARIZE() Function in Power BI – Analytics Insights Ninja (wordpress.com)If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍&nbsp;Best regardsDenis&nbsp;Blog: WhatTheFact.biFollow me: twitter.com/DenSelimovic

nowhitescreens · Answer

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 time2) Create column that compare above with all previous dates&nbsp;3 Create&nbsp; column that specifies "Yes" if nr of accounts have changed per rowPasting the DAX below. Sorry for not translating it to english, but I trust you get the point.&nbsp;&nbsp;1)#Antal konton vid transaktionen =var _personnummer = 'TABELL'[PERS_NR]var _BESLDATUM = TABELL[BESLDATUM]returnCALCULATE(&nbsp;&nbsp;&nbsp; DISTINCTCOUNTNOBLANK(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL[KONTONUMMER]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL[PERS_NR] = _personnummer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; TABELL[BESLDATUM] &lt;= _datum))&nbsp;2)#Antal konton vid tidigare transaktioner =var _personnummer = 'TABELL'[PERS_NR]var _BESLDATUM = TABELL[BESLDATUM]returnCALCULATE(&nbsp;&nbsp;&nbsp; DISTINCTCOUNTNOBLANK(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL[KONTONUMMER]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABELL[PERS_NR] = _personnummer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; TABELL[BESLDATUM] &lt;= _datum-1))&nbsp;&nbsp;3)#Nytt konto registrerat under månaden =if(&nbsp;&nbsp;&nbsp; TABELL[Antal konton vid transaktionen]-TABELL[Antal konton vid tidigare transaktioner]&lt;&gt;0, "Ja", "Nej")

Forum Discussion

Count distinct values AND Filter by Calendar (AND maybe Summarize them)?

2 Replies

Related content

DISTINCT over SUMMARIZE

Summarize with distinct values

Parameter in summarize

DISTINCT UNION & Summarized Combined.

Don't Summarize....still summarizes...?