how to remove the duplicate rows when add + 0 in dax to replace the blank to 0

[oakfootballclub]

Hi experts, I created a table visual and there may be some blank values so I created the measure like this:

// VAR cal=

DIVIDE(

CALCULATE(SUM(fact_usd[ner_week]))

,

CALCULATE(SUM(fact_usd[ser_week]))) +0

// RETURN

// IF(ISBLANK(cal),0,cal)

there is a +0 or I use ISBLANK so in the table visual it seems all the rows becomes shown rather only one required row, do you know how to solve it? Thank you.

[rajendraongole1]

Hi @oakfootballclub -Create the below measure

To display the table visual only the relevant rows without unnecessary duplication.

Measure =
VAR cal =
DIVIDE(
CALCULATE(SUM(fact_usd[ner_week])),
CALCULATE(SUM(fact_usd[ser_week]))
)
RETURN
IF(ISBLANK(cal), 0, cal)

Hope it helps

[oakfootballclub]

Thanks, actually I have tried by this method and it does not successfully get the result I need

[Anonymous · Accepted solution]

Hi @oakfootballclub ,

I think your issue should be casued by your data model.

According to your code, in cal all blank value will return 0 so there is no blank value basd on this logic.

// VAR cal=
DIVIDE(
CALCULATE(SUM(fact_usd[ner_week]))
,
CALCULATE(SUM(fact_usd[ser_week]))) +0
// RETURN
// IF(ISBLANK(cal),0,cal)

If you need to use ISBLANK() function, I think you need to remove +0 from cal as rajendraongole1  mentioned.

If his code still couldn't achieve your goal, you can try to add more logic in IF().

For example:

Measure 2 = 
VAR cal =
DIVIDE(
CALCULATE(SUM(fact_usd[ner_week])),
CALCULATE(SUM(fact_usd[ser_week]))
)
RETURN
IF(ISBLANK(cal) && MAX(DimDate[Date]) <=EOMONTH(TODAY(),0), 0, cal)

Here I let my code to show data until this month. I hope it could help you solve your issue.

If this reply still couldn't help you solve your issue, please share a sample file with us.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @oakfootballclub - Ok, share with some sample data with expectations , will try from our end.