Count that ignores part of a filter

[emilyrichardson]

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]

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? 

[v-linyulu-msft]

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:

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

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.

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.

[emilyrichardson]

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
1	22/10/2024	Door Card - Water Ingress
1	22/10/2024	Door Stone Film - Film Inclusion / Defect
1	22/10/2024	Bonnet - Dull Paint
1	22/10/2024	BSL - Orange Peel, Flow Structure
1	22/10/2024	Fender - Rear - Scratch / Scuffed
1	22/10/2024	B-Pillar - Gap
1	22/10/2024	Buttress Panel - Clam - Rear - Loose / Misassemble
2	20/10/2024	Door Card - Water Ingress
2	20/10/2024	Door Stone Film - Film Inclusion / Defect
2	20/10/2024	BSL - Orange Peel, Flow Structure
2	20/10/2024	B-Pillar - Gap
2	20/10/2024	Buttress Panel - Clam - Rear - Loose / Misassemble
2	20/10/2024	Dirty Air Duct - Inclusion - Contrast
2	20/10/2024	Fender - Rear - Corroded / Oxidised
3	18/10/2024	Scuttle Panel - Fender Infill Panel - Scratch / Scuffed
3	18/10/2024	Dirty Air Duct - Inclusion - Contrast
3	18/10/2024	Fender - Rear - Corroded / Oxidised
3	18/10/2024	Door Card - Water Ingress
3	18/10/2024	Door Stone Film - Film Inclusion / Defect
3	18/10/2024	Fender - Rear - Scratch / Scuffed
3	18/10/2024	B-Pillar - Gap
4	21/10/2024	B-Pillar - Gap
4	21/10/2024	Buttress Panel - Clam - Rear - Loose / Misassemble
4	21/10/2024	Dirty Air Duct - Inclusion - Contrast
4	21/10/2024	Door Card - Water Ingress
4	21/10/2024	Bonnet - Dull Paint
4	21/10/2024	Fender - Rear - Scratch / Scuffed

[v-linyulu-msft · 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])

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.

 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]

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.