Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
elguestca1
Frequent Visitor

Count based on condition

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?

elguestca1_0-1699428501779.png

 

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1699593539776.png

I created a slicer based on the ID:

vjunyantmsft_1-1699593559328.png

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:

vjunyantmsft_2-1699593633730.png

 

vjunyantmsft_3-1699593638855.png

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.





View solution in original post

2 REPLIES 2
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1699593539776.png

I created a slicer based on the ID:

vjunyantmsft_1-1699593559328.png

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:

vjunyantmsft_2-1699593633730.png

 

vjunyantmsft_3-1699593638855.png

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.





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!! 🙂 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors