Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter on measure

Hey everyone, 

 

Is there a way to count rows or distinctcount the articles where difference <0 ? Difference is a measure, based on several other measures. 

I only need one return with 3 as result. I do understand that I can't use a card visual, because you can't aggregate when you want to filter on a measure. But isn't there a workaround? 

 

Need Help
  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    Hello there Anonymous ! Not sure if this is what you need but:

    Measure = 
CALCULATE(
    COUNTROWS( 
        FILTER(
            SUMMARIZE(
                'Articles',
                'Articles'[Aggregation_criteria],
                "Diff", [Difference]
             ),
            [Difference] < 0
        )
    )
)

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You can use FILTER with a measure. As it is an iterator it will perform the check for each row of the given table, e.g.

    Num < 0 = COUNTROWS( FILTER('Table', [Difference] < 0) )

    and you should be able to use that on a card visual as well.

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

    Let's assume there row filter context is from the column Table[Field] and the measure is [difference]
    You can try:

    Count Field =
COUNTX (
    CALCULATETABLE ( VALUES ( Table[Filed] ), FILTER ( Table, [Difference] < 0 ) ),
    Table[Field]
)

    It should work in a card visual

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    4 years ago

    Hello there Anonymous ! Not sure if this is what you need but:

    Measure = 
CALCULATE(
    COUNTROWS( 
        FILTER(
            SUMMARIZE(
                'Articles',
                'Articles'[Aggregation_criteria],
                "Diff", [Difference]
             ),
            [Difference] < 0
        )
    )
)

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you guys! goncalogeraldes , your suggestion worked perfectly, so I will be using that measure