Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate distict count based on slicer selection in Power BI Desktop

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.

 

 

2018-11-27_9-04-03 FOrum Power BI.png

 

 

 

3 ACCEPTED SOLUTIONS
Kristjan76
Responsive Resident
Responsive Resident

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

View solution in original post

Anonymous
Not applicable

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 🙂

 

2018-11-27_11-15-43 Forum 3.png

 

 

 

View solution in original post

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)

View solution in original post

14 REPLIES 14
Kristjan76
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

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?

 

2018-11-27_11-00-07 DAX by Date.png

 

 

 

 

 

 

 

Anonymous
Not applicable

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 🙂

 

2018-11-27_11-15-43 Forum 3.png

 

 

 

Anonymous
Not applicable

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?

Anonymous
Not applicable

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)

 

 

 

2018-11-28_11-16-03 Formu.png

 

 

 

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

Anonymous
Not applicable

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.

 

 

 

 

Anonymous
Not applicable

One of the option I am thinking of are :

 

  • Use below DAX to calculate the "Category" measure,
    Sector (Inquiries) = 
        Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]))
  • And add "Category" as a second X-axis to the Quarter.  When Expand the report and selected "Category" result displayed. (refer to the below screen print)
  • Questions:  In the expanded bar chart the X-axis value display both "Quarter" and "category".   Is it possible that I keep only the X-axis value of Quarter and remove the "banking services"?  Thank you.

 

 

 

 

 

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)
Anonymous
Not applicable

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?

 

2018-11-28_12-55-21 forum 5.png

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)

Anonymous
Not applicable

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)

 

 

2018-11-30_10-32-42 Forum Issue.png

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.