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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...