Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
smpa01
Super User
Super User

ISEMPTY to evaluate only for connected values

Following is my sample data (pbix attached) and data model

 

cap1.PNG

 

Capture.PNG

 

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. 

cap3.PNG

https://drive.google.com/file/d/1AUx1iUutm6PUOO6r0lKK3rHo3x4CyyNq/view?usp=sharing

@CNENFRNL

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
3 REPLIES 3
v-xiaoyan-msft
Community Support
Community Support

Hi @smpa01 ,

 

Maybe you could try using filter instead of Dax like this:

vcaitlynmstf_0-1631268938232.png

 

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

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

 

cap4.PNG

 

But is there an elegant way to solve this by passing a filter in Measure 3?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.