How to calcuate disticntcount based on another column

[ramanapowerbi]

Dear All,

I am new to the DAX, can some one help me how to calculate the disctintcount based on date column.

 Exampel: 

Cosat A= 9999 (Needs to distribute 9999 cost to new calculated column

Date               Customer type           cost     Expected column(it is distrubution)

01.01.2017        abc                         1000      (cost A /(Disticnt count of Customer types by date 01.01.2017)

01.01.2017        xyz                          2000       (cost A /(Disticnt count of Customer types by date 01.01.2017)

01.01.2017        xyz1                        3000     (cost A /(Disticnt count of Customer types by date 01.01.2017)

01.01.2017        efg                         1000       (cost A /(Disticnt count of Customer types by date 01.01.2017)

01.01.2017        efg1                        1000      (cost A /(Disticnt count of Customer types by date 01.01.2017)

01.01.2017                                       1000      needs to exclude blank customer type

02.01.2017        efg                          2000      (cost B /(Disticnt count of Customer types by date 02.01.2017)

02.01.2017        efg                          3000       (cost B /(Disticnt count of Customer types by date 02.01.2017)

 I am trying following does not help

Disticnt count of Customer types by date = calculate(DISTINCTCOUNT(custtable[cust]);FILTER(all(custtable[Date]);custtable[Date] =Max (calculatetable[Date]))) or EARLIER..etc

None of them give me correct disticnt count.

Appreciate your answer

Thanks,

Ramana.

[v-shex-msft · Accepted solution]

Hi @ramanapowerbi,

You can refer to below link if it suitable for your requirement:

Expected =
IF (
    [Customer type] <> BLANK (),
    DIVIDE (
        9999,
        CALCULATE (
            DISTINCTCOUNT ( Sample2[Customer type] ),
            FILTER (
                ALL ( Sample2 ),
                [Date] = EARLIER ( Sample2[Date] )
                    && [Customer type] <> BLANK ()
            )
        ),
        0
    )
)

Notice: blank type not record in calculation, it will also skip blank type row.

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @ramanapowerbi,

You can refer to below link if it suitable for your requirement:

Expected =
IF (
    [Customer type] <> BLANK (),
    DIVIDE (
        9999,
        CALCULATE (
            DISTINCTCOUNT ( Sample2[Customer type] ),
            FILTER (
                ALL ( Sample2 ),
                [Date] = EARLIER ( Sample2[Date] )
                    && [Customer type] <> BLANK ()
            )
        ),
        0
    )
)

Notice: blank type not record in calculation, it will also skip blank type row.

Regards,

Xiaoxin Sheng

[ramanapowerbi · Accepted solution]

Thank you very much.

---

@v-shex-msft wrote:

Hi @ramanapowerbi,

You can refer to below link if it suitable for your requirement:

Expected =
IF (
    [Customer type] <> BLANK (),
    DIVIDE (
        9999,
        CALCULATE (
            DISTINCTCOUNT ( Sample2[Customer type] ),
            FILTER (
                ALL ( Sample2 ),
                [Date] = EARLIER ( Sample2[Date] )
                    && [Customer type] <> BLANK ()
            )
        ),
        0
    )
)

Notice: blank type not record in calculation, it will also skip blank type row.

Regards,

Xiaoxin Sheng

---

---

@v-shex-msft wrote:

Hi @ramanapowerbi,

You can refer to below link if it suitable for your requirement:

Expected =
IF (
    [Customer type] <> BLANK (),
    DIVIDE (
        9999,
        CALCULATE (
            DISTINCTCOUNT ( Sample2[Customer type] ),
            FILTER (
                ALL ( Sample2 ),
                [Date] = EARLIER ( Sample2[Date] )
                    && [Customer type] <> BLANK ()
            )
        ),
        0
    )
)

Notice: blank type not record in calculation, it will also skip blank type row.

Regards,

Xiaoxin Sheng

---