Create Measure without altering context

[Hoping]

I have a table - TABA  which has a PERSON_ID, few other columns, INDICATOR column that has 2 values - YES and NO and an AMOUNT column.

I have a table visual with PERSON_ID, all othercolumns and adding AMOUNT_MEASURE finally.

AMOUNT_MEASURE = CALCULATE( SUM('TABA'[Amount]), 'TABA'[Indicator] = "YES" )

Now some PERSON_IDs don't have rows where INDICATOR = "YES"

I want to display AMOUNT_MEASURE and if there are no rows for a person where INDICATOR = "YES"  then I want to display ZERO

EDIT: I understood the problem. The AMOUNT_MEASURE filters the table to only rows that have "YES" and there are None in some cases, so the entire row in the table visualisation disappears.

How can I calculate AMOUNT_MEASURE if ther exist at least one row with with INDICATOR = "YES"  else show ZERO  without affecting other columns in the table i.e. without affecting grain of the table?

[sjoerdvn]

Try adding "+ 0" to your measure.

[Hoping]

This didn't work.  The measure never returns.

[sjoerdvn · Accepted solution]

If the measure doesn't return a value, even with the  "+ 0" it means that Xiaoxin Sheng is right and you need additional (dimension) tables to force evaluation for every combination.
Note that I wouldn't necessarily add one table with each combination, but more likely dimension tables for PERSON, INDICATOR and CATEGORY.

[Anonymous]

Hi @Hoping ,

If you mean these records not real include in your table, you may need to create a new table with all combinations of two categories. Then you can compare them with 'YES' records count to get blank/missed records amounts.

In addition, Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng