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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
emilyrichardson
Frequent Visitor

Count that ignores part of a filter

I have a dashboard, which I plan to use as a sort of replacement power point presentation. 

The idea behind it is that a unique identifer is searched and the presentation shows you all the pictures on the product that relate to a fault. This part of the presentation works fine, however, the issue I have is I want a table underneath the pictures that shows the count of the faults that we have had the past 3 days. So lets say a fault seen in the product is the it is missing a badge, I want the table to count how many times we have seen that fault across all products in the last 30 days. The issue currently is that as the prod number is filtering across the whole page is is only counting the faults for that product. If I edit interactions then shows all faults seen across the last 30 days, it doesn't take into the accounts only seen on that product. Is there a way to get around this? 

 

This is what the table pulls if I have blocked the interaction between the prod filter in the top right hand corner, it shows every fault seen in the last 30 days. When this specific product only had 3 faults, so I would just need it to count the total number of faults seen for those three faults over the last 30 days. 

emilyrichardson_0-1729498426138.png

 

1 ACCEPTED SOLUTION

Hi, @emilyrichardson 

 

Thank you for your prompt reply and corrections. This has helped me gain a better understanding of your needs.

 

1.Firstly, regarding your question, “Also, if I am making a virtual table for the prod numbers, should I be removing duplicates and creating a relationship between my tables?” it’s clear that you are eager to learn. The answer is that it is not advisable to establish a relationship between the virtual table and the original data, as such relationships can affect the output of measures.

 

2.Secondly, concerning the need to remove duplicates, my recommendation is that it is unnecessary, as Power BI will automatically handle this in the visual objects. This is one of the intelligent features of Power BI. However, if you do wish to remove duplicates, you can refer to the following link:

DISTINCT (column) function (DAX) - DAX | Microsoft Learn
 

3.Returning to your initial question, I have revised the solution as follows:

Firstly, create a calculated table that references only the Prod Number column and use it as a slicer.

Table 2 = VALUES('Table'[Prod Number])

vlinyulumsft_0-1729753034889.png

Secondly, use the following measures:

MEASURE = 
VAR select1 =
    MAX ( 'Table 2'[Prod Number] )
VAR cc =
    CALCULATETABLE (
        VALUES ( 'Table'[Fault] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Prod Number] = select1 )
    )
VAR cc2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DateCredited ]
                >= TODAY () - 30
                && 'Table'[Fault] = MAX ( 'Table'[Fault] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Prod Number] ),
        IF ( MAX ( 'Table'[Fault] ) IN cc, cc2, BLANK () ),
        cc2
    )

Finally, I hope the results below will meet your needs.

vlinyulumsft_1-1729753093546.png

vlinyulumsft_2-1729753093548.png

 If you have any further questions, please feel free to reach out. It is my pleasure to assist you.

 

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
emilyrichardson
Frequent Visitor

I have added the DAX to my dashboard: 

FaultCountForFaultTypes =
CALCULATE(
    COUNTROWS(CrebitMasterV2),
    FILTER(
        CrebitMasterV2,
        CrebitMasterV2[Score and Fault] IN VALUES('MPA Fault Collection'[Score and Fault])
        && CrebitMasterV2[CreditDateTime].[Date]>= TODAY() - 30
    )
)
But I am having trouble, when I filter to different prod numbers the bottom table doesn't seem to be showing me the faults for that vehicle. I have included an example. I have edited the interaction between the prod filter in the top right corner, not too sure what I am doing wrong? 
emilyrichardson_0-1729501474091.png

 

  

Thanks for the reply from PavanLalwani , please allow me to provide another insight:

Hi, @emilyrichardson 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

vlinyulumsft_0-1729583358510.png

2.Secondly, by creating a calculated table, you can extract the product ID column you need as a slicer, ensuring that the other original data remains unaffected by the slicer.

Table 2 = VALUES('Table'[ProductID])

vlinyulumsft_1-1729583406606.png

3. Below are the measure I've created for your needs:

FaultCountLast30Days = 
VAR SelectedProduct = MAX('Table 2'[ProductID])
VAR cc1=CALCULATETABLE (
        VALUES ( 'Table'[FaultType]),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ProductID] = SelectedProduct  )
    )
RETURN
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(ALL('Table'),
        'Table'[FaultDate] >= TODAY()-30&&
        'Table'[FaultType] IN cc1
    ))

4.Here's my final result, which I hope meets your requirements.

vlinyulumsft_2-1729583453428.png

5.Below are some posts addressing similar issues, which I hope will be helpful to you:

Solved: Creating a filter that keeps & counts all other re... - Microsoft Fabric Community

 

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Please find the attached pbix relevant to the case.

 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you for your reply Leroy Lu, maybe I haven't been clear. This is some example data. So in my power bi I want a drop down box for the prod number. Then I want a table that shows the fault specific to that fault, so for prod number 1 it would show: Door Card - Water Ingress, Door Stone Film - Film Inclusion / Defect, Bonnet - Dull Paint, BSL - Orange Peel, Flow Structure, Fender - Rear - Scratch / Scuffed, B-Pillar - Gap and Buttress Panel - Clam - Rear - Loose / Misassemble. And the table will count the last time it has seen each of these faults for all prods, so with this data it should return: Door Card - Water Ingress (4), Door Stone Film - Film Inclusion / Defect (3), Bonnet - Dull Paint, BSL - Orange Peel, Flow Structure (2) , Fender - Rear - Scratch / Scuffed (3) , B-Pillar - Gap (4) and Buttress Panel - Clam - Rear - Loose / Misassemble (3). Also if I am making a virtual table for the prod numbers, should I be removing duplicates and creating a relationship between my tables? 

Prod Number DateCredited Fault
122/10/2024Door Card - Water Ingress
122/10/2024Door Stone Film - Film Inclusion / Defect
122/10/2024Bonnet - Dull Paint
122/10/2024BSL - Orange Peel, Flow Structure
122/10/2024Fender - Rear - Scratch / Scuffed
122/10/2024B-Pillar - Gap
122/10/2024Buttress Panel - Clam - Rear - Loose / Misassemble
220/10/2024Door Card - Water Ingress
220/10/2024Door Stone Film - Film Inclusion / Defect
220/10/2024BSL - Orange Peel, Flow Structure
220/10/2024B-Pillar - Gap
220/10/2024Buttress Panel - Clam - Rear - Loose / Misassemble
220/10/2024Dirty Air Duct - Inclusion - Contrast
220/10/2024Fender - Rear - Corroded / Oxidised
318/10/2024Scuttle Panel - Fender Infill Panel - Scratch / Scuffed
318/10/2024Dirty Air Duct - Inclusion - Contrast
318/10/2024Fender - Rear - Corroded / Oxidised
318/10/2024Door Card - Water Ingress
318/10/2024Door Stone Film - Film Inclusion / Defect
318/10/2024Fender - Rear - Scratch / Scuffed
318/10/2024B-Pillar - Gap
421/10/2024B-Pillar - Gap
421/10/2024Buttress Panel - Clam - Rear - Loose / Misassemble
421/10/2024Dirty Air Duct - Inclusion - Contrast
421/10/2024Door Card - Water Ingress
421/10/2024Bonnet - Dull Paint
421/10/2024Fender - Rear - Scratch / Scuffed

Hi, @emilyrichardson 

 

Thank you for your prompt reply and corrections. This has helped me gain a better understanding of your needs.

 

1.Firstly, regarding your question, “Also, if I am making a virtual table for the prod numbers, should I be removing duplicates and creating a relationship between my tables?” it’s clear that you are eager to learn. The answer is that it is not advisable to establish a relationship between the virtual table and the original data, as such relationships can affect the output of measures.

 

2.Secondly, concerning the need to remove duplicates, my recommendation is that it is unnecessary, as Power BI will automatically handle this in the visual objects. This is one of the intelligent features of Power BI. However, if you do wish to remove duplicates, you can refer to the following link:

DISTINCT (column) function (DAX) - DAX | Microsoft Learn
 

3.Returning to your initial question, I have revised the solution as follows:

Firstly, create a calculated table that references only the Prod Number column and use it as a slicer.

Table 2 = VALUES('Table'[Prod Number])

vlinyulumsft_0-1729753034889.png

Secondly, use the following measures:

MEASURE = 
VAR select1 =
    MAX ( 'Table 2'[Prod Number] )
VAR cc =
    CALCULATETABLE (
        VALUES ( 'Table'[Fault] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Prod Number] = select1 )
    )
VAR cc2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DateCredited ]
                >= TODAY () - 30
                && 'Table'[Fault] = MAX ( 'Table'[Fault] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Prod Number] ),
        IF ( MAX ( 'Table'[Fault] ) IN cc, cc2, BLANK () ),
        cc2
    )

Finally, I hope the results below will meet your needs.

vlinyulumsft_1-1729753093546.png

vlinyulumsft_2-1729753093548.png

 If you have any further questions, please feel free to reach out. It is my pleasure to assist you.

 

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PavanLalwani
Resolver II
Resolver II

To achieve this, where you need a table to count all instances of specific faults in the last 30 days for *only the types of faults* associated with a specific product but not limited to that product, you can follow this approach in Power BI (assuming you're using it as the dashboard tool):

### Steps:
1. **Create a Measure for the Fault Count**: You need a measure that counts the faults but filters out the product-specific filter only for the table while still keeping the filter on fault types. Here's how you can define this in Power BI:

```DAX
FaultCountForFaultTypes =
CALCULATE(
COUNTROWS(FaultsTable),
FILTER(
FaultsTable,
FaultsTable[FaultType] IN VALUES(FilteredProductFaultsTable[FaultType])
&& FaultsTable[Date] >= TODAY() - 30
)
)
```

Explanation:
- `FaultsTable[FaultType] IN VALUES(FilteredProductFaultsTable[FaultType])`: This part ensures that only faults of the same type as the faults of the selected product are counted.
- `FaultsTable[Date] >= TODAY() - 30`: This keeps the date filter to only the last 30 days.

2. **Filter the Faults Based on the Product's Faults**:
- `FilteredProductFaultsTable`: This is a virtual table containing only the faults of the currently filtered product (from your product selection).
- You essentially want to maintain the fault type filter for the product but remove the product-specific filtering for the count, so the measure will count occurrences of those fault types across all products.

3. **Apply the Measure to the Table**:
- Use this measure in your table visualization to display the count of faults of the same type (but across all products) within the last 30 days.

### Edit Interactions:
- Ensure that the interaction between the product filter and the table is turned off in your report, so the table displays the fault count for all products but still respects the fault types associated with the selected product.

By following this approach, you’ll be able to see the count of faults for those fault types across all products in the last 30 days without restricting the count to the selected product but still filtering the relevant fault types.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.