Forum Discussion

Hoping's avatar
Hoping
Helper III
2 years ago
Solved

Create Measure without altering context

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?
 
 
Need Help
  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • Hoping's avatar
      Hoping
      Helper III
      2 years ago

      This didn't work.  The measure never returns.

      • sjoerdvn's avatar
        sjoerdvn
        Solution Sage
        2 years ago

        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.