Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in BI by (17.6k points)

I'm counting the number of unique IDs per month in a given timeframe and I've encountered two strange things: 1. Looking for the same thing but using two different approaches (value for each month and cumulative value month by month) gives different values. See the screenshot below. 2. When You'll add by hand values in the first column (monthly value) the result is 868 when PowerBI summarize it - it's 864 o_O

any ideas?

DAX Formulas below:

Y-1 Kandydaci = CALCULATE(

    distinctcount(getDataForTeb[ID_DANE_OSOBOWE]);

    DATESBETWEEN(

        getDataForTeb[Złożenie podania];

        DATE(YEAR(now())-1;4;1);

        IF(DATE(YEAR(NOW())-1;MONTH(NOW());DAY(NOW()))<=DATE(YEAR(NOW())-1;11;30);

            DATE(YEAR(NOW())-1;MONTH(NOW());DAY(NOW()));DATE(YEAR(NOW())-1;11;30)));

            ISBLANK(getDataForTeb[REZYGNACJA_DATA]))

Y-1 Kandydaci cumulative = CALCULATE(

    DISTINCTCOUNT(getDataForTeb[ID_DANE_OSOBOWE]);

    FILTER(

        ALL (getDataForTeb);

        AND (

            getDataForTeb[Złożenie podania] <= MAX(getDataForTeb[Złożenie podania])-364;

            AND (

                getDataForTeb[Złożenie podania] <= DATE(YEAR(NOW())-1; 11; 30);

                getDataForTeb[Złożenie podania] >= DATE(YEAR(NOW())-1; 4; 1)

            )

        )

    );

    ISBLANK(getDataForTeb[REZYGNACJA_DATA])

)

Another interesting example just from a while ago: different file, no DAX involved:

 

1 Answer

0 votes
by (47.2k points)
  • It is all because of DISTINCTCOUNT(). It has counted the number of distinct values for the [ID_DANE_OSOBOWE] column in each month, but when the measure is evaluated for all months, it does not double count the values which appear in more than one month.

Simplified:

| ID | Month |

+----+-------+

| 1  | March |

| 1  | April |

  • When you have a measure My Measure = DISTINCTCOUNT(tbl[ID]) for each month the value will be 1, but when you do a distinct count for all months then the value will still be 1 as there is only one distinct value.

Related questions

0 votes
1 answer
asked Dec 17, 2020 in Python by laddulakshana (12.7k points)
0 votes
1 answer
asked Sep 15 in BI by dev_sk2311 (43.2k points)
0 votes
1 answer
asked Aug 25 in BI by dev_sk2311 (43.2k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.7k users

Browse Categories

...