Count ID's, in each month, that verify a condifition

[ST]

Hi everyone!

I'm trying to build a measure that counts ID's of an element that meet a certain criteria by month and by the total of the year.

The measure i got at this point is this one:

Total IDs = 
CALCULATE (
    DISTINCTCOUNT ( facViagensPermanentes[t_motr] ),
    FILTER (
        VALUES ( facViagensPermanentes[t_motr] ),
        facViagensPermanentes[Total de Km's]
                        >= [Km's Contr.]
                        * ( 1
                        - [Tolerância de Produtividade Value] / 100 )
    )
)

This measure is working OK on the month granularity, but in the total Year granularity this measure is counting all ID's, for some reason, and so its wrong on this last granularity level.
How would one change this measure, in order to count, in the year granularity, the number of ID's that meet this criteria:

facViagensPermanentes[Total de Km's]
                        >= [Km's Contr.]
                        * ( 1
                        - [Tolerância de Produtividade Value] / 100 )

and maintain, at the month granularity, the same results given by the measure [Total IDs] that i indicated before ?

Also [Total de Km's] is a measure calculated this way:

Total de Km's = SUM( facViagensPermanentes[t_kvzcr] ) + SUM( facViagensPermanentes[t_kmcr] )

 and [Km's Contr.] is another measure, calculated this way:

[Km's Contr.] = 
IF( HASONEFILTER( facViagensPermanentes[Fornecedor] ) && HASONEFILTER( facViagensPermanentes[Rota] ),
    BLANK(),
IF (    
        AND (
            HASONEFILTER ( dimdate[Mes] ),
            HASONEFILTER ( facViagensPermanentes[t_motr] )
        )
    ,
    LOOKUPVALUE (
        dimservico[Kms],
        dimservico[t_serv], (
            LOOKUPVALUE (
                dimVeiculos[t_serv],
                dimVeiculos[t_motr], FIRSTNONBLANK ( facViagensPermanentes[t_motr], TRUE () )
            )
        )
    ),
    BLANK ()
)
) 

If need more info pls let me know.

Thanks in advance.  🙂

[v-yulgu-msft]

Hi @ST,

It's hard to imagine your scenario with current description. It would be better you could illustrate requirement with sample data (table structure and detailed data) and show us your desired output.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[ST]

Hi everyone!

@v-yulgu-msft, I shared a link to an example PBI file regarding this issue: Problem Example

Model Structure:



Tables Structure:

Please let me know if u want any more info.

Thank is advance for your help.

🙂

[ST]

Can someone shed some light on me ?

I'm struggling with this problem since i posted here this morning 😞

Thanks.