topic Re: Calculating unique values by month in DAX in DAX Commands and Tips

Calculating unique values by month in DAX

JRA21_13_19_25 — Thu, 28 Oct 2021 18:53:51 GMT

HELLO,

I want to create a DAX formula that will calculate the total UNIQUE products by Month and Availabilty using the table below.

So one formula will calculate total unique products for Online and this should give a value of 5 and for Instore it should give a value of 6.

I started by using the CALCULATE Function below but I don't know how to factor in finding the unique products by month:

Total Unique Products for Online = CALCULATE(DISTINCTCOUNT(Products[Product ID]),Products[Availability]="Online",

Please can someone kindly help me out with this.

Thanks in Advance!

Re: Calculating unique values by month in DAX

Greg_Deckler — Thu, 28 Oct 2021 18:57:11 GMT

JRA21_13_19_25 Try:

Measure = VAR __Table = SELECTCOLUMNS('Table',"Month",[Month],"Product ID",[Product ID],"Availability",[Availability]) RETURN COUNTROWS(DISTINCT(__Table))

Re: Calculating unique values by month in DAX

JRA21_13_19_25 — Thu, 28 Oct 2021 19:10:55 GMT

Greg_Deckler THANK YOU SO MUCH GREG!!! IT WORKED😁😃

Re: Calculating unique values by month in DAX

AlexisOlson — Fri, 29 Oct 2021 00:58:23 GMT

This seems like a good situation for SUMMARIZE.

Measure1 = COUNTROWS ( SUMMARIZE ( Table1, Table1[Month], Table1[Product ID], Table1[Availability] ) )