Not able to Count result of if statement by measure

[mrsanjaykumar]

Hi Friends,  In following table visual, column "Datastore Issues" is result of a if measure that is calculating following things:

(1) If current month and previous month usage is greater than 80% than show text "Oustanding"

(2) If previous month is less then 75% then show as "New".

Now in next step, I wany to take count of "New" and "Oustanding" items for that month and want to show in the cards. 

My table name is Fact_DataStores

Column that has that if measure is DataStore Issues

New Issues = CALCULATE(COUNTROWS('Fact_DataStores'),FILTER(Fact_DataStores, [DataStore Issues] = "New"))

Outstanding Issues = CALCULATE(COUNTROWS('Fact_DataStores'),FILTER(Fact_DataStores, [DataStore Issues] = "Outstanding"))

When I am using above measures,

(1) the Outstanding Issues is not working at all and New Issues measure showing incorrect value. It is showing 7 instead of 4. I want to creat those measure and will use that in the card visual but they are  not counting correctly. I tried multiple things but it is not working. Oustanding Issues measure is not working at all, Is it beacuse I am using Previousmonth measure as well in creating DataStore Issue measure or what is reason? Any help will solve this critical issue for multiple reports for me.

[Anonymous]

Hi @mrsanjaykumar ,

I made the following attempt and you can check the results below:

Dec = var _Current = MAX('Table'[Current])
var _Previous = MAX('Table'[Previous])
RETURN IF(_Current>=80&&_Previous>=80,"Outstanding",IF(_Previous<80,"New",0))

New Issue = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Dec]="New"))

Outstanding Issue = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Dec]="Outstanding"))

Best regards,
Community Support Team_ Scott Chang

[mrsanjaykumar]

Thank you Sir for taking your time and kind effort to help me! I tried but it is now working as I am using Previsoumonth measure to get previous month value in my table. Although, to test your idea, I created a separate file where it works but against column not with measure.

Sir, is there any way to make it can work for measure as well? or I can get previou month value in my table for a field that is percentage?  I tried to create custome column as well in my table using dax to get previous month column but it shows blank value in that as well. Can you suggest anything on that, please?

[Anonymous]

Hi @mrsanjaykumar ,

Try below expression:

Current Measure = MAX('Table'[Current])

Previous Measure = MAX('Table'[Previous])

Dec = var _Current = [Current Measure]
var _Previous = [Previous Measure]
RETURN IF(_Current>=80&&_Previous>=80,"Outstanding",IF(_Previous<80,"New",0))

New Issue = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Dec]="New"))

Outstanding Issue = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Dec]="Outstanding"))

Best regards,
Community Support Team_ Scott Chang

[mrsanjaykumar]

Hi @Anonymous,

I tried by implementing it into my main report but it is not working:

Current Measure = AVERAGE('Report_DXC-Servers_Disk'[Percent Disk Used])

Previous Month = CALCULATE(AVERAGE('Report_DXC-Servers_Disk'[Percent Disk Used]), PREVIOUSMONTH(VALUES('Calendar'[Date])))

Previous Measure = [Previous Month]

Dec = var _Current = [Current Measure]

var _Previous = [Previous Measure]

RETURN IF(_Current>=.8&&_Previous>=.8,"Outstanding",IF(_Previous<.8,"New",0))

New Issue2 = CALCULATE(COUNT('Report_DXC-Servers_Disk'[ShortName]),FILTER('Report_DXC-Servers_Disk',[Dec]="New"))

Outstanding Issue = CALCULATE(COUNT('Report_DXC-Servers_Disk'[ShortName]),FILTER('Report_DXC-Servers_Disk',[Dec]="Outstanding"))

New Issue2 measure is giving me count of all the records in the table instead of  just new items and Outstanding Issue measure is showing zero.

The solution is not working if I am using PREVIOUSMONTH measure into the measue Dec that you suggest. @Anonymous, please advice what is worng with it. or any other solution that I use to get same result.

Regards,

Sanjay

[Anonymous]

Hi @mrsanjaykumar ,

You can try using ALLSELECTED as a condition for filtering the table.For example

New Issue = CALCULATE(COUNT('Table'[ID]),FILTER(ALLSELECTED('Table'[ID]),[Dec]="New"))

Best regards,
Community Support Team_ Scott Chang

[mrsanjaykumar]

Hi @Anonymous ,

I tried this as well but it is also not working if I am using PreviousMonth meaure to get previosu month value.

This is the measure I am using for previous month value

previousmonth = CALCULATE(AVERAGE(Sheet1[Current]), PREVIOUSMONTH(Calendar[Date]))

Dec = var _Current = MAX(Sheet1[Current])

var _Previous = previousmonth

RETURN IF(_Current>=50&&_Previous>=50,"Outstanding",IF(_Previous<50,"New",0))

Please suggst.