Count based on condition

[elguestca1]

Hello, I am quite new to PBI and I am having issues with one measure. I need to count how many ID's are having "Max date" greater than "First date" excluding blank values. For example 99046 should count as 1, because in 4th row "Max date" is greater than "First date".  Same with 96413 and 95525. 94402 should count as 0, because blank dates are not counted. The data are in 2 tables Table 1 has ID and Max date, Table 2 has ID and First Date. The relationship is many to many.  Maybe you have any ideas how to do it?

[v-junyant-msft · Accepted solution]

Hi @elguestca1 ,

I'm sorry, but because you have two tables and the table relationships are many-to-many, the feature is not possible in this case. I can only manually merge the two tables you give into one table and then use DAX to calculate them.
Then I need to correct one mistake you made in the issue you asked, id 95525 should count as 0 instead of 1 the same as id 99046:

I created a slicer based on the ID:

You can use the following DAX to create a measure:

CountID = 
CALCULATE(
    COUNTROWS('Table3'),
    NOT(ISBLANK('Table3'[Max Date])),
    NOT(ISBLANK('Table3'[First Date])),
    'Table3'[Max Date] > 'Table3'[First Date],
    'Table3'[ID] IN VALUES(Table3[ID])
)

And the final output is shown in the following figure:

Then I strongly discourage you from using a many-to-many relationship, it has a lot of limitations, you can choose to merge two tables manually, or you can add a bridge table to optimize many-to-many relationships, you can refer to the following documents:
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
Many-to-many relationship guidance - Power BI | Microsoft Learn

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elguestca1]

Hello, @v-junyant-msft ,

thank you for your reply and sorry for the mistake. Haste makes its effect... I did have an idea of using bridge table, but I thought maybe there is a way without that, because there are more tables than these 2, so I may need to created more of bridge tables or merge more tables into one. Anyway, your solution works fine for now, thank you very much!! 🙂