Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |