Forum Discussion

bvy's avatar
bvy
Icon for Helper V rankHelper V
3 years ago
Solved

Constant Measure breaking my One-to-Many Relationship - Why?

Very simple data model and report here to demonstrate my issue. I have a Person table and a City table with a one-to-many relationship on CityID as shown below. I entered the data manually.    ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    bvy Maybe:

     

    MyMeasure = 
    VAR __CityID = MAX('Person'[CityID])
    VAR __CityID2 = MAX('City'[CityID])
RETURN
    IF(__CityID = __CityID2, "Active",BLANK())

    As for why, it is because you coded your measure as a constant. So every row will return that constant value for your measure and thus your current results.

     

NotebookEnjoyer's avatar
NotebookEnjoyer
Icon for Advocate II rankAdvocate II
10 months ago

I recently stumbled upon this issue and find it frankly ridiculous. That the measure is calculated for every row, fine, but why is Daniel suddenly living in every city?!

However, I used as a rather simple and performant workaround:

IF(
    COUNTROWS(granular_table) < 1,
    BLANK(),
    [desired_measure]
)

Elsewhere I found a workaround with

IF(
    ISBLANK(
        SELECTEDVALUE(granular_table[id_column])
    ),
    BLANK(),
    [desired_measure]
)

This works fairly well but has the disadvantage of returning BLANK for totals or any case where some degree of aggregation is present and desirable.