Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I've been staring at this issue and need a second set of eyes. I've got two tables, TABLE1 a parent table with unique values and TABLE2 with values I need to slice and work with. What I'm trying to do is have a list of locations (sorted by parent location) that have no values in Table 2. I've tried a few things but can't get it to work.
Table 1 | |||
Parent# | Parent Name | Loc# | Location Name |
29 | Pittsburgh | 9929 | Region LDR |
29 | Pittsburgh | 2980 | Murrysville |
29 | Pittsburgh | 2979 | Wexford Flats |
28 | Ohio | 2890 | Cedar Cliff |
28 | Ohio | 2889 | Carlisle |
28 | Ohio | 2888 | Simpson Ferry |
28 | Ohio | 2887 | Hampden Center |
28 | Ohio | 2886 | Lemoyne |
28 | Ohio | 2885 | Coventry Mall |
32 | Virgina | 2830 | Westlake |
32 | Virgina | 2829 | Hudson |
31 | Maryland | 2761 | Catonsville |
31 | Maryland | 2760 | Carney |
31 | Maryland | 2759 | Lombard Street |
31 | Maryland | 2758 | Waugh Chapel |
31 | Maryland | 2757 | Annapolis Towne Centre |
31 | Maryland | 2755 | Baywoods |
Table 2 | ||||
Parent# | Parent Name | Loc# | Location Name | Value |
29 | Pittsburgh | 9929 | Region LDR | 123698 |
29 | Pittsburgh | 2980 | Murrysville | 5555 |
28 | Ohio | 2888 | Simpson Ferry | 2000 |
28 | Ohio | 2887 | Hampden Center | 400 |
32 | Virgina | 2830 | Westlake | 398 |
31 | Maryland | 2759 | Lombard Street | 33558 |
32 | Virgina | 2830 | Westlake | 350 |
31 | Maryland | 2759 | Lombard Street | 4000 |
29 | Pittsburgh | 9929 | Region LDR | 200 |
29 | Pittsburgh | 2980 | Murrysville | 1000 |
28 | Ohio | 2888 | Simpson Ferry | 40 |
28 | Ohio | 2888 | Simpson Ferry | 100 |
I've tried
Zero's = IF(Table 2[Value]=0,"zero","Not Zero")
As a calculation but it's not returning the correct answer.
Thanks Everyone!
We can also achieve this by leveraging RELATEDTABLE. Create a calculated column using
No Record's = COUNTROWS(RELATEDTABLE(Table 2[Value])) and then filter "No Record's" = Blank or 0 to display the records with no values in Table 2.
Please let me know if this worked.
@Greg_Deckler: Huge fan of yours and I started following the Power BI community because of your answers. You are a great asset to the PBI community. Thanks for all your tips and answers.
I believe what you want for your calculation (in a calculated column in Table1) would be:
Zero's = IF(ISBLANK(CALCULATE(SUM(Table 2[Value]))),"zero","Not Zero")
Assuming you have a relationship on "#Loc" columns.
Hi,
Presuming that you've joined both table by the "Loc#" field, you could just bring in "Loc#" from the parent table and "location name" from the location table into a Power BI table visual and then filter the visual to only show where "location name" is blank.
No that is not working (that was the first thing I did), I'm using the locations from the Table 1 and trying to tie the values into Table 2, but you're seeing a simplified version of the data. There's a lot more complexity here that is likely causing the problem, but I've sort of stared at this for so long I can't see the answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |