topic Re: Count of distinct products on max date per location in DAX Commands and Tips

Count of distinct products on max date per location

jony_gloeckner — Mon, 13 Mar 2023 09:33:20 GMT

Hello Community,

I need help with DAX measure to calculate distinct products on last date for each location.

This is fact table:

and here is where I got with current measure:

Thing that bothers me is that I need to show total of 4 ( 1 product for location B and 3 products for location B).

My current dax measure is:

VAR LastDateForCustomer=

ADDCOLUMNS(

DISTINCT( Fact[Location]),

"@LastDate", MAX(Fact[Date])

)

VAR FilterCustomerDate=

TREATAS(

LastDateForCustomer,

Locations[Location],

Calendar[Date]

)

Return

CALCULATE(

DISTINCTCOUNT(Fact[Product]),

FilterCustomerDate

)

Fact table:

Location	Date	Product
A	24.11.2022	2
A	24.11.2022	10
A	24.11.2022	11
B	3.11.2022	5
B	20.10.2022	8
A	22.9.2022	2
B	22.9.2022	5
B	15.9.2022	5
B	8.9.2022	5
B	1.9.2022	6
A	30.6.2022	2
A	5.5.2022	4
A	28.4.2022	2
A	14.4.2022	3
A	17.3.2022	1
A	10.2.2022	7
A	10.2.2022	9
A	27.1.2022	4

Thank you all!

Re: Count of distinct products on max date per location

Greg_Deckler — Mon, 13 Mar 2023 10:03:45 GMT

jony_gloeckner So, your sample data ony has 3 distinct values for Product for Location B (5, 8 and 6). Unless I am not understanding the requirement.

Re: Count of distinct products on max date per location

jony_gloeckner — Mon, 13 Mar 2023 10:57:17 GMT

Greg_Deckler, yes, but in total I need to show number of distinct products on all locations (4). So products 2,10 and 11 for location B and product 5 for location A.

Re: Count of distinct products on max date per location

Greg_Deckler — Mon, 13 Mar 2023 11:00:34 GMT

jony_gloeckner First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Count of distinct products on max date per location

andhiii079845 — Mon, 13 Mar 2023 11:07:02 GMT

If only total is the probem, we discuss it some hours ago. Its a typical "total" problem.

https://community.powerbi.com/t5/Desktop/Measures-are-getting-mapped-correctly-But-Grand-Total-is-showing/m-p/3127539#M1058546

Re: Count of distinct products on max date per location

jony_gloeckner — Mon, 13 Mar 2023 11:11:55 GMT

andhiii079845 Greg_Deckler thank you guys, I was unsure if I did something wrong, but it seems like total is the issue.
So what you say basically is that I can't get this figure as a total or a KPI value?
Because when I put measure in KPI, it also doesnit show correct count.

Greg_DecklerI also voted for your idea.

Re: Count of distinct products on max date per location

andhiii079845 — Mon, 13 Mar 2023 11:16:32 GMT

You have to use a HASONEFILTER:

IF(HASONEFILTER("Location"),"Your count measure","Other calcuation to get total")

Re: Count of distinct products on max date per location

Greg_Deckler — Mon, 13 Mar 2023 11:25:23 GMT

jony_gloeckner You can, but at that point it is kind of a different problem (actually a bit easier to solve than measure totals. It's a measure aggregation issue at that point. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Count of distinct products on max date per location

jony_gloeckner — Mon, 13 Mar 2023 11:46:40 GMT

Greg_DecklerI am using this measure:
CALCULATE(
DISTINCTCOUNT(Fact[Product]),
FilterCustomerDate
)
Isn't that same as the one in your pattern? I don't see what am I missing..

Re: Count of distinct products on max date per location

Greg_Deckler — Mon, 13 Mar 2023 11:57:52 GMT

jony_gloeckner I still don't understand where you are getting your desired numbers from. In the sample data provided, there are 8 distinct products for Location A and 3 distinct products for Location B. There is no overlap between Locations in terms of products. To me, that would mean that the total should be 11. But you are saying 4 and I don't know where that is coming from. Unless you are saying 3 for Location A on November 24th and 1 for Location B on November 3rd. If that is the case, then it is this for a Card visual (or KPI). And it also works in a Matrix visual as well. My PBIX file is attached below signature.

Distinct Products = VAR __Table = ADDCOLUMNS( SUMMARIZE('Table',[Location],"__MaxDate", MAX('Table'[Date])), "__Distinct", VAR __Date = [__MaxDate] VAR __Location = [Location] VAR __Result = COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER(ALL('Table'),[Location] = __Location && [Date] = [__MaxDate]), "__Products",[Product] ) ) ) RETURN __Result ) VAR __Result = SUMX(__Table,[__Distinct]) RETURN __Result

Re: Count of distinct products on max date per location

jony_gloeckner — Tue, 14 Mar 2023 08:33:16 GMT

Greg_Deckler, I thought that this formula is correct one, but if we add location C and we have same product on maximum date - formula doesn't recognize that given product is on location A.
Here is example:

I need to get total of 4 distinct products.
Here is .pbix:
Distinct Products

Re: Count of distinct products on max date per location

andhiii079845 — Tue, 14 Mar 2023 08:29:05 GMT

Add it via ondrive or dropbox or google drive and share the link. 🙂

Re: Count of distinct products on max date per location

jony_gloeckner — Tue, 14 Mar 2023 08:30:08 GMT

andhiii079845 thx for the tip 🙂

Re: Count of distinct products on max date per location

tamerj1 — Tue, 14 Mar 2023 10:23:13 GMT

Hi jony_gloeckner
Please refer to attached sample file with the proposed solution

Product Count 2 = SUMX ( VALUES ( Locations[Location] ), MAXX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( DISTINCTCOUNT ( 'Fact'[Product] ) ) ) )

Re: Count of distinct products on max date per location

jony_gloeckner — Tue, 14 Mar 2023 10:57:56 GMT

Hi tamerj1 ,

thank you for your suggestion, but dax measure doesn't count distinct products.
I added to example location C that has same product as location B on max date, and it adds that product to total sum.

I need to get 4 as count of products.

Pbix file is below:

Community 567.pbix

Re: Count of distinct products on max date per location

tamerj1 — Tue, 14 Mar 2023 11:33:21 GMT

jony_gloeckner
Please refer to attached

Product Count 3 = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( GENERATE ( VALUES ( Locations[Location] ), TOPN ( 1, CALCULATETABLE ( 'Fact' ), 'Fact'[Date] ) ), "@Product", [Product] ) ) )

Re: Count of distinct products on max date per location

jony_gloeckner — Tue, 14 Mar 2023 12:07:07 GMT

That's it! Great! Thank you a lot!

Re: Count of distinct products on max date per location

Greg_Deckler — Tue, 14 Mar 2023 14:35:28 GMT

jony_gloeckner The link doesn't work for me.