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.
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 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |