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

[Nowhitescreens]

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

[selimovd]

Hey @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:

 Creating Summary Tables Using DAX SUMMARIZE() Function in Power BI – Analytics Insights Ninja (wordp...

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 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Nowhitescreens]

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")