Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

EXCEPT ALLSELECTED

HI!!!!   I have a table with costumers and year, and i have to count the numbers of costumers accordingly the year selected in the slicer, and count the numbers of costumers that less than the year...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please try the following measure:

     

    INATIVO = 
VAR selectyear = SELECTEDVALUE ( costumers[year purchase] )
VAR _count =
    CALCULATE (
        COUNT ( costumers[Costumers] ),
        FILTER (
            ALL ( costumers[year purchase] ),
            costumers[year purchase] > selectyear
        )
    )
RETURN
    IF ( selectyear <> BLANK (), _count )

                    

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Anonymous Please try:-

Selected_year =
CALCULATE (
    COUNT ( costumers[Costumers] ),
    FILTER (
        costumers,
        costumers[year purchase] > SELECTEDVALUE ( costumers[year] )
            && costumers[year purchase]
                <= SELECTEDVALUE ( costumers[year] ) + 3
    )
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Srry samarth, bus doesn't worked too. 

The result of the count it's like considering only 2020 and 2022. 

 

 

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Anonymous is it possible for you to share your PBIX file after removing sensitive data? and also the expected output?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello!

      Sorry but i cant share the pbix. But this is a screenshot of the table that im trying to do. 

      So "ANO VENDA" is the Year Purchase,  "ATIVO"  is active and "INATIVO" is inative.

      In this case, I'm filtering the year 2019, to bring in active customers, this table value matches, but the value of inactive ones does not, which would be all other years forward, if in this case I had filtered 2020, it would bring the inactive ones from 2021 and 2022, and so on.

      Following this logic, I put the total value of customers on the card to the side. So the inactive numbers should be the actives - the total amount, right?

       

       

       

       

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        Please try the following measure:

         

        INATIVO = 
VAR selectyear = SELECTEDVALUE ( costumers[year purchase] )
VAR _count =
    CALCULATE (
        COUNT ( costumers[Costumers] ),
        FILTER (
            ALL ( costumers[year purchase] ),
            costumers[year purchase] > selectyear
        )
    )
RETURN
    IF ( selectyear <> BLANK (), _count )

                        

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.