Count of distinct products on max date per location

[jony_gloeckner]

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! 

[tamerj1]

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] ) )
    )
)

[jony_gloeckner]

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 

[tamerj1 · Accepted solution]

@jony_gloeckner 
Please refer to attached

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

[jony_gloeckner]

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

[jony_gloeckner]

@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.

[Greg_Deckler]

@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.

[jony_gloeckner]

@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..

[Greg_Deckler]

@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

[jony_gloeckner]

@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

[Greg_Deckler]

@jony_gloeckner The link doesn't work for me.

[andhiii079845]

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

[jony_gloeckner]

@andhiii079845 thx for the tip 🙂

[andhiii079845]

You have to use a HASONEFILTER:

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

[andhiii079845]

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-sh...

[jony_gloeckner]

@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.

[Greg_Deckler]

@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

[Greg_Deckler]

@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.