Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Following is my sample data (pbix attached) and data model
I want to
bring site and month from keyTbl in a matrix viz
and
show if there any single row exists in condition tbl for which there exists any single row by site with condition=X or condition = Y
and
I want that measure to return relvant results only for sites that exist in site tbl
I can write a measure as following that gives me the desire result
Measure 3 = ISEMPTY(CALCULATETABLE('condition','condition'[condition] IN {"X","Y"},ALLEXCEPT(keyTbl,keyTbl[site])))
Now, how can I ask DAX to filter the above measure only for sites that are in only site tbl. From the screenshot below, I don't want the results to be returned for highlighted sites
Thank you in advance.
https://drive.google.com/file/d/1AUx1iUutm6PUOO6r0lKK3rHo3x4CyyNq/view?usp=sharing
Hi @smpa01 ,
Maybe you could try using filter instead of Dax like this:
If I have not understood your needs correctly, please do not hesitate to inform me.
Hope it helps,
Community Support Team _ Caitlyn Yan
Hi Kaitlyn,
Thanks for looking into it. unfortunately, this is not the I was looking forward to. My table has 2.5M+ rows and constantly compounding. It is not possible to put a manual harcoded filter there unless I can do this with an explicit measure.
Thanks
I can resolve this with the following measures
keySiteInSiteTbl =
IF (
NOT ISEMPTY (
CALCULATETABLE ( keyTbl, TREATAS ( VALUES ( keyTbl[site] ), site[site] ) )
),
1,
-1
)
XYinConditionTbl =
IF (
NOT ISEMPTY (
CALCULATETABLE (
'condition',
'condition'[condition] IN { "X", "Y" },
ALLEXCEPT ( keyTbl, keyTbl[site] )
)
),
1,
-1
)
Measure4 =
VAR _0 =
IF ( [keySiteInSiteTbl] = 1, [XYinConditionTbl] )
VAR _1 =
IF (
_0 = 1,
"for this site there exists a single row with X or Y in condition tbl",
IF (
_0 = -1,
"for this site there does not exist a single row with X or Y in condition tbl"
)
)
RETURN
_1
But is there an elegant way to solve this by passing a filter in Measure 3?
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 |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |