March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to 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.
I have added the DAX to my dashboard:
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.
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 |
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
91 | |
79 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
72 | |
55 |