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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ito_c
Frequent Visitor

Force "0" where I don't have variable return

Hi Everyone, 

 

I have a small dataset (in the below table). 

Fiscal_YearFiscal_MonthSubject_TitleEvaluation
FYear20FMonth11Algebra IGood
FYear20FMonth11Algebra IIBad
FYear20FMonth11Algebra IIIBad
FYear20FMonth12StatisticNeutral
FYear21FMonth01Social ScienceBad
FYear21FMonth01AlgorithmsGood

 

With DAX formula (creadits to Tahreem24) was possible return the percentage for each "Evaluation" by "Fiscal_Month". 

 

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_)

 

Fiscal_YearFiscal_MonthSubject_TitleEvaluation%
FYear20FMonth11Algebra IGood33%
FYear20FMonth11Algebra IIBad33%
FYear20FMonth11Algebra IIIBad33%
FYear20FMonth12StatisticNeutral100%
FYear21FMonth01Social ScienceBad50%
FYear21FMonth01AlgorithmsGood50%

 

So, now I need to return in a Card only the Neutral percenage for last Fiscal_Month. I don't have for the last Fiscal_Month any value for "Neutral" Evaluation, because of this the KPI card is returnig the value 100% for previus Fiscal_Month (FMonth12). I tryed to use as base some formulas to fill with "0" in case of sales when sales value is empty, but not work as expected. 

 

I need just filter "Evaluation" as "Neutral" and return the last "Fiscal_Month" value, but in this case does anybody knows how to return "0" value when I don't have "Neutral" Evaluation for the last Fiscal_Mont "FMonth01"?

1 ACCEPTED SOLUTION

Hi, @Ito_c 

If you want to fill with "0" in case of sales when sales value is empty, you need to add a separate dimension table for Evaluation, and then try measure mentioned by amitchandak.

20.png

21.png

22.png

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Ito_c , have tried

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_,0)

 

or

 

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_,0)+0

Hi @amitchandak, include just "0" doesn't work in this case, keeping returning the previous Fiscal_Month where there is a value for column Evaluation:

 

ForumDoubt4.JPG

 

I looking for this: 

ForumDoubt5.JPG

Hi, @Ito_c 

If you want to fill with "0" in case of sales when sales value is empty, you need to add a separate dimension table for Evaluation, and then try measure mentioned by amitchandak.

20.png

21.png

22.png

Best Regards,
Community Support Team _ Eason

Thank you @v-easonf-msft works fine. Thank you @amitchandak for your support too!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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