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.
I have 2 tables with measures. These tables are similar but are unrelated. My page has a table that includes measures from Table 1 and Table 2. Each table's measures are completely independent of anything on the other table.
If I apply a filter (slicer) to Table 1 (using a value that does not exist in Table 2), the results stemming from Table 2 are unaffected.
Is there a way to change this so the results from Table 2 would all be 0?
For the image above, these are the two respective formulas for the Count columns with no filters applied:
_TABLE1 MIM Overall Count = IF(ISBLANK(COUNT('TABLE1 MIM Library'[Number])),0,COUNT('TABLE1 MIM Library'[Number]))
_TABLE 2 MIM Overall Count = IF(ISBLANK(COUNT('TABLE 2 MIM Library'[Number])),0,COUNT('TABLE 2 MIM Library'[Number]))
Let's further assume the filter to be applied is to Table 1 (which has no matching value in Table 2): 'Table 1'[Item] = "XYZ" There are actually about 7 possible filters that could be applied to the table. Ideally, I'd like to be able to say "if Table 1 has any filter applied, then 0, otherwise the formula above" but I don't know how to achieve that.
Help?
Solved! Go to Solution.
Hi:
You can use this to start off your measure:
Function = ISFILTERED(<TableNameOrColumnName>) It is a true/false type of function. Similiar too HASONEVALUE
New Measure = IF(ISFILTERED(Table1), 0, your measure goes here)
I hope that's enough to help!
I did a variation on this using individual columns separated by the OR clause ( || ) since there were other filters that might be applied that should not cause a 0 value in Table 2. The was able to apply the IF clause to the overall formulas. Worked great!
_TABLE 1 Filters Applied? = IF(ISFILTERED('TABLE 1 MIM Library'[CI]) || ISFILTERED('TABLE 1 MIM Library'[CI Parent Domain]) || ISFILTERED('TABLE 1 MIM Library'[CI Domain]) || ISFILTERED('TABLE 1 MIM Library'[Number]) || ISFILTERED('TABLE 1 MIM Library'[Short Description]),1,0)
Hi:
You can use this to start off your measure:
Function = ISFILTERED(<TableNameOrColumnName>) It is a true/false type of function. Similiar too HASONEVALUE
New Measure = IF(ISFILTERED(Table1), 0, your measure goes here)
I hope that's enough to help!
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 |
---|---|
39 | |
19 | |
17 | |
16 | |
15 |
User | Count |
---|---|
50 | |
23 | |
20 | |
17 | |
16 |