Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hoping
Helper II
Helper II

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 = CALCULATESUM('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?
 
 
1 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.

View solution in original post

4 REPLIES 4
sjoerdvn
Super User
Super User

Try adding "+ 0" to your measure.

This didn't work.  The measure never returns.

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.

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors