Forum Discussion

bitofanewb's avatar
bitofanewb
Frequent Visitor
5 months ago
Solved

IF statement logical test needs split by DISTINCT

Hi, 

I need to count the number of distinct [Qual] where [_BestX CY] < [_Prev Yr Avg].

 

[Qual] is a column, [_BestX CY] and [_Prev Yr Avg] are both measures - all within the same table.

The below only looks at the [Qual] column as a whole. Is there a way I can use DISTINCT to adjust it, or does it need completely rewritten?

 

IF([_BestX CY] < [_Prev Yr Avg], COUNT('B_Rep_Formula_Data'[Qual]), 0)
 

Many thanks in advance to anyone who is able to help.
  • johnt75's avatar
    johnt75
    5 months ago

    I think you could use a measure like

    Count distint qual =
COUNTROWS (
    FILTER ( DISTINCT ( 'Table'[Qual] ), [_BestX CY] < [_Prev Yr Avg] )
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    5 months ago

    I think you could use a measure like

    Count distint qual =
COUNTROWS (
    FILTER ( DISTINCT ( 'Table'[Qual] ), [_BestX CY] < [_Prev Yr Avg] )
)
    • bitofanewb's avatar
      bitofanewb
      Frequent Visitor
      5 months ago

      Yes, that's exactly it. I had most of that in another attempt, I was just missing FILTER between COUNTROWS and DISTINCT. 

      Thanks so much for your really quick reply and help, it's greatly appreciated.