Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 |
Solved! Go to 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]
)
)
)
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] ) )
)
)
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:
@jony_gloeckner
Please refer to attached
Product Count 3 =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
GENERATE (
VALUES ( Locations[Location] ),
TOPN (
1,
CALCULATETABLE ( 'Fact' ),
'Fact'[Date]
)
),
"@Product", [Product]
)
)
)
That's it! Great! Thank you a lot!
@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.
@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.
@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..
@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
@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
@jony_gloeckner The link doesn't work for me.
Add it via ondrive or dropbox or google drive and share the link. 🙂
Proud to be a Super User!
You have to use a HASONEFILTER:
IF(HASONEFILTER("Location"),"Your count measure","Other calcuation to get total")
Proud to be a Super User!
If only total is the probem, we discuss it some hours ago. Its a typical "total" problem.
Proud to be a Super User!
@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.
@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
@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.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |