Forum Discussion

Smamwise's avatar
Smamwise
Frequent Visitor
2 years ago

Calculated column based on a measure in another table

Hi!

 

I have a issue which I cannot wrap my head around.

 

I have four tables:


Dimension Item,
Fact StockEvent,

Dimension EventDate,
Dimension EventType

Those are connected as 

 

 

In StockEvent table I have a measure:

CountFilteredEvents = CALCULATE ( COUNTROWS(StockEvent), FILTER ( StockEventType,[EventType] in {"T1","P1","P2"} ) )
This works fine, gives me the count of rows with correct filter. Works with date filter also in report.
 
In Item table I would need a calculated column as:
Has Stock Events? = IF ( [CountFilteredEvents] >= 1, "has events", "no events" )

Though this gives me "has events" on every row, even if there are none. This seems to disregard the date filter.

Measure with the same dax works fine.

 

I know that the column is calculated at a row level and the measure at a filter level but I'm not sure what to do with that information. I would appreciate any assistance 

Need Help

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    There are a few misconceptions in the question.  Measures are for visuals, calculated columns are for adding columns to tables - they are calculated at refresh time.  Calculated columns do not change according to visual filters.

    It is not a good idea to create a calculated column which uses a measure.

    --

    "In StockEvent table I have a measure" - a measure has a 'home' table.  It means nothing. A measure has no value until it's added to a visual.

    --

    Re-write these both as calculated columns or use power query.