Forum Discussion

afbraga66's avatar
afbraga66
Icon for Helper III rankHelper III
4 years ago
Solved

Filtering a constant value

Hello,


I am new to Power BI, so my question is that why this measure example returns 3 rows in a table, while I expected it to return only 1 row. I do not understand how to filter constant values.  Thank you.

 

Best regards,

André

constant
Filtering
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， afbraga66 ，
    According to offical document, CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

    https://docs.microsoft.com/en-us/dax/calculate-function-dax


    Calculate ([column]) or average or count... as 1 is a constant.so we could change it to:

     

     

    Measure = IF(MAX('Dim_PLANT'[Plant_Code])="P058",1)

     

     

    The final show:

    Or 

     

     

    Measure 2 = CALCULATE(COUNT(Dim_PLANT[Plant_Code]),FILTER('Dim_PLANT',[Plant_Code]="P058"))

     

     

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi afbraga66 

    try replace FILTER with KEEPFILTERS but delete table name reference 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    afbraga66 

    Ok. Please try

    SUMX (
    VALUES ( PlantCode ), 

    IF ( PlantCode = "p058", 1 )

    )

    • afbraga66's avatar
      afbraga66
      Icon for Helper III rankHelper III
      4 years ago

      tamerj1 Ok, that works. I'll try to apply that concept to what I really have to do. That was just an example to express the idea of what I was trying to do. Still, can you explain why the other methods do not work? Thank you.

       

      Best regards, André

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        afbraga66 

        CALCULATE replaces the filter with the new filter inside it. So it replaces P051 with P059 and so on for all plant codes. What I don't understand why the KEEPFILTERS function didn't work with constant value as it KEEPFILTERS keeps the original filter and the intersection between the old the new filter leaves blank tables at all plant codes except P059. 
        With SUMX we manually return blank if the code is not P059

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    You are calculating 1, in whatever case.

    It gives you back 1.

    If you calculat a measure like countrows(table), and the countrows filter by case

    then it will get different numbers.

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， afbraga66 ，
    According to offical document, CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

    https://docs.microsoft.com/en-us/dax/calculate-function-dax


    Calculate ([column]) or average or count... as 1 is a constant.so we could change it to:

     

     

    Measure = IF(MAX('Dim_PLANT'[Plant_Code])="P058",1)

     

     

    The final show:

    Or 

     

     

    Measure 2 = CALCULATE(COUNT(Dim_PLANT[Plant_Code]),FILTER('Dim_PLANT',[Plant_Code]="P058"))

     

     

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.