Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
5 years ago

ISEMPTY to evaluate only for connected values

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

@CNENFRNL

Need Help

3 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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