Forum Discussion

sergioquerido's avatar
sergioquerido
Icon for Helper I rankHelper I
2 years ago

IF function not respecting the Filter when value is TRUE

Hello,

I have the following calculated measure but when I added the IF function, it stoped to respect the filter '0_SessonFilter'[Unit Number]. As you can see in attached figure, when the fuction IF reurns True (1), it does not respect the filter and is shown in table.

DayTypeTeam_TotalDistance =
VAR _dayType = [0_ParameterTeam_DayType]
VAR _average =
CALCULATE(CALCULATE(
AVERAGEX(SUMMARIZE(DBASE,'0_DateFilter'[Date],'0_SessonFilter'[Unit Number], Settings_2024[Nickname]), CALCULATE(SUM(DBASE[Total Distance]))), FILTER(DBASE, DBASE[Session]= _dayType), FILTER(DBASE, DBASE[Typology] = "Match" || DBASE[Typology] = "Training")), ALLEXCEPT(DBASE, Settings_2024[Position], Settings_2024[Nickname]))



RETURN
IF(_average <= 0, 1, DIVIDE([Day_TotalDistance], _average))

How to solve it?

Thanks

 

SQ

Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi sergioquerido - you can try with calculate function in if statement as below:

     

    Modified Measure:

    DayTypeTeam_TotalDistance =
    VAR _dayType = [0_ParameterTeam_DayType]
    VAR _average =
    CALCULATE(
    AVERAGEX(
    SUMMARIZE(
    DBASE,
    '0_DateFilter'[Date],
    '0_SessonFilter'[Unit Number],
    Settings_2024[Nickname]
    ),
    CALCULATE(SUM(DBASE[Total Distance]))
    ),
    FILTER(DBASE, DBASE[Session] = _dayType),
    FILTER(DBASE, DBASE[Typology] = "Match" || DBASE[Typology] = "Training")
    )

    RETURN
    IF(
    _average <= 0,
    CALCULATE(1, '0_SessonFilter'[Unit Number]),
    CALCULATE(DIVIDE([Day_TotalDistance], _average), '0_SessonFilter'[Unit Number]) 
    )

     

    Hope this helps.

    • sergioquerido's avatar
      sergioquerido
      Icon for Helper I rankHelper I
      2 years ago

      Thanks but it is not working, giving back an error: Calculation error in measure 'DBASE'[DayTypeTeam_TotalDistance]: It is not possible to convert a value "1 GK" text type to True/False.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi sergioquerido ,

     

    Could you please provide more details about your question? Such as pbix file. It will help us understand the question better.

     

     

    Best regards,

    Mengmeng Li