Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I have a table (refer to screen print) and a slicer filter with the 'Customer Name'.
When a customer name is selected, I would like to pass the 'Customer Category' value to a mesure that will calculate the distinct count of 'claim number' of that Category.
For example, when "AA" or "BB"is selected, i would like the mesure to calculate how many distict count of claim number for 'Category A' and it is 8. When "DD" is selected, the measure will calculate the discount count of "Catetogy B" and it is 8. I hope this make sense and any help will be appreciated. Thank you.
Solved! Go to Solution.
Just top of my head, I think this should work
Distinct Claim Numbers =
VAR custCat = Values(Table[Customer Category])
RETURN
CALCULATE(DISTINCTCOUNT(Table[Claim Number]),ALL(Table),custCat) or instead of ALL(Table) you can say ALL(Table[Customer])
Updates:
Category_Count2 = VAR custCat = Values('Source Data'[Customer Category]) RETURN CALCULATE(DISTINCTCOUNT('Source Data'[Claim Number]),ALL('Source Data'[Customer Name]),custCat)
Now it works for different creation year 🙂
Firm (Inquiries) =
calculate(
DISTINCTCOUNT('Cases'[ Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
Sector (Inquiries)_Rev =
VAR SECTOR=
CALCULATETABLE(
VALUES('Cases'[Sector Name (merge)]),
USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date])
ALL(Dates)
)
RETURN
Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR)
Just top of my head, I think this should work
Distinct Claim Numbers =
VAR custCat = Values(Table[Customer Category])
RETURN
CALCULATE(DISTINCTCOUNT(Table[Claim Number]),ALL(Table),custCat) or instead of ALL(Table) you can say ALL(Table[Customer])
Thank you. The DAX works.
I maybe not explain clear and complete. If I would like to calculate "Category Distinct Count" by 'Creation Date' or by 'Claim Issues' , then current DAX seems could not refelct the number for different creation year. Does this make sense?
Updates:
Category_Count2 = VAR custCat = Values('Source Data'[Customer Category]) RETURN CALCULATE(DISTINCTCOUNT('Source Data'[Claim Number]),ALL('Source Data'[Customer Name]),custCat)
Now it works for different creation year 🙂
One more question, in real world, the measure of 'Category Distinct Count' is more complicated due to the 'Userelationship' to activate the relationship of Claim table with Dates(also calendar) table.
No Syntex error disply on Power BI but not sure whether each filters are working as they should. Thank you.
Category_Count Real = VAR custCat = Values('Source Data'[Customer Category]) RETURN CALCULATE(DISTINCTCOUNT('Source Data'[Claim Number]),
USERELATIONSHIP('source data'[Date of Claim], Dates[Date]),
ALL('Source Data'[Customer Name]),custCat)
This should work fine, you just have to try it and verify it. Did you have question?
Yes, I have probmes applying the DAX into my real report.
I understand that the 'Category Distict Count' by Year or Quarter should be same/consistant for different customers who belong to the same Category. But this seems not true in my situation.
I have applied the same DAX in my measurement and refer to below coding for deail.
And I have applied the new measure in a report that display the individual customer data Vs 'Category' for the most recent five quarters. Using slicer, I selected three different customers that belong to same 'Category' ('banking service' in this example) but each time the new measurement in the report are not consistantly displayed. For example, in #1 example, 18-Q1, 18-4 for the "Category" are missing; in #2 Example, 19-Q1 is missing and #3 example are good and all quarters have dispaly data properly. Refer to screen print.
I could not explain what has happened in the background and why "Category' calculation display different for different slicer selction. Did anybody experenced the same? Any ideas or helps would be appreciated. Thank you.
Firm (Inquiries) = calculate( DISTINCTCOUNT('Cases'[ Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
Sector (Inquiries)_Rev = VAR SECTOR=VALUES('Cases'[Sector Name (merge)]) RETURN Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR)
I might be wrong about this, but it seems to me that selected firm does not have any claims in the period where Sector (Inquiries)_Rev is blank under the active date relationship. This would mean that the SECTOR variable would be blank and therefore it returns blank.
You can try this:
Firm (Inquiries) 2 =
DISTINCTCOUNT('Cases'[ Case Number])
When i extract more details from the report (refer to below screenshot) and Fiscal Year is missing. Is this the possible reason that "Category" not showing for 18-Q4?
How to resolve this issue? I do not understand " Firm(Inquiry) 2" in your reply. I mean, should I use 'Firm (inquiry) 2' to replace "Firm(Inquiry)" in the report? Please advise, thank you.
One of the option I am thinking of are :
Sector (Inquiries) = Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]))
My version was just for debugging.
Just try this,
Firm (Inquiries) = calculate( DISTINCTCOUNT('Cases'[ Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date])) Sector (Inquiries)_Rev = VAR SECTOR=CALCULATETABLE(VALUES('Cases'[Sector Name (merge)]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date])) RETURN Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR)
This version works and not works.
It works as long as the 'Firm' is not blank. If the 'Firm' is blank then the Sector is not showing. Does this make sense?
Firm (Inquiries) =
calculate(
DISTINCTCOUNT('Cases'[ Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
Sector (Inquiries)_Rev =
VAR SECTOR=
CALCULATETABLE(
VALUES('Cases'[Sector Name (merge)]),
USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date])
ALL(Dates)
)
RETURN
Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR)
Thank you for your help the updated version works well. Now I added "Issues" dimension to the same report (refer to screen print). Same issue again - How to make the report display the "Category" by "Issue" when "Firm" is blank.
I tried below DAX, but this not works all times.
FIRM (Closed) = Calculate( DISTINCTCOUNT('Cases'[Case Number]), filter ('Cases', 'Cases'[Date Case Completed]<>blank() ) ) Sector (Closed) =
VAR SECTOR=
CALCULATETABLE(
VALUES('Cases'[Sector Name (merge)]),
All(Dates),
All('Cases'[Issue])
)
Return
Calculate ([FIRM (Closed)], All('Cases'[Firm Name]), SECTOR)
Hi there,
I am sorry but do not understand what the problem is, can you explain it better, i.e. what is not working? I cannot see what is wrong in the screenshot.
Best regards,
Kristjan
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |