topic Create Measure without altering context in DAX Commands and Tips

Create Measure without altering context

Hoping — Thu, 06 Jun 2024 08:00:17 GMT

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?

Re: Create Measure without altering context

Anonymous — Fri, 07 Jun 2024 01:02:24 GMT

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

Re: Create Measure without altering context

sjoerdvn — Fri, 07 Jun 2024 13:10:49 GMT

Try adding "+ 0" to your measure.

Re: Create Measure without altering context

Hoping — Tue, 11 Jun 2024 08:34:24 GMT

This didn't work. The measure never returns.

Re: Create Measure without altering context

sjoerdvn — Tue, 11 Jun 2024 10:03:32 GMT

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.