topic Re: Measure returning the correct value for one end of a relationship, and incorrect for the other end in DAX Commands and Tips

Measure returning the correct value for one end of a relationship, and incorrect for the other end

nfuids — Tue, 02 Aug 2022 15:23:58 GMT

Hi folks!

I'm rather new to Power BI, did a LOT of reading, almost all the learning path available and halfway through The Definitive Guide to DAX!

I'm currently trying to create my first measure.

My model is rather simple for now.. it consists of 1 table called 'UseCase' and a table called 'Feature'. There is a many-to-many relation between the two table since a UseCase acts as a parent that can have multiple features, but a feature could also be part of multiple use case.

In the feature table, I have a column called "Status Raffinement" which might have "Pas démarré, En cours or Terminé" value. The measure I'm trying to create is to know the % of feature that are completed (Status = Terminé) for any given use case. So if a use case has 4 features and one of them is completed while the other 3 are either not started or in progress (Status = Pas démarré or En cours), the measure should say 25%.

So far so good, I did manage to do that which works good. When I put the UseCase and the measure in a table, I get the correct numbers.

The problem arises when I use a matrix and put the feature too. The numbers are good at the use case level, but at the feature level, it repeat the same number. So if 25% of the features are completed, it will display 25% for each feature instead of 100% for those completed and 0% for those not started or in progress.

I'm pretty sure it has something to do with context, but I just can't figure it out.

Here is my DAX Expression for the measure

Pct Raffinement Termine := VAR nbrKidsTotal = COUNTROWS ( RELATEDTABLE ( Feature ) ) VAR nbrKidssRaffinementTermine = COUNTROWS ( FILTER ( RELATEDTABLE ( Feature ), Feature[Statut Raffinement] = "Terminé" ) ) VAR pctRaffinementTermine = DIVIDE ( nbrKidssRaffinementTermine, nbrKidsTotal, 0 ) RETURN pctRaffinementTermine

Below, you can see that one of the use case has 5 children, two of which are terminé, so that makes 40% and the value at the use-case level is correct. I would have expected to see 100% for the 2 completed features and 0% for the others. My guess is that I use RELATEDTABLE because the measure was initially intented for the Use-Case, but I don't see how I could make it work for both Use-Case and Feature at the sametime.

Regards,

Mart

Re: Measure returning the correct value for one end of a relationship, and incorrect for the other end

nfuids — Wed, 03 Aug 2022 19:05:29 GMT

It seems I created a custom field instead of a measure!

My code below works as expected, with a small error, but still, works pretty much ok!

Pct Realisation Termine = VAR nbrKidsTotal = COUNTROWS ( RELATEDTABLE ( Feature ) ) VAR nbrKidsTermine = COUNTROWS ( FILTER ( RELATEDTABLE ( Feature ), Feature[Statut Réalisation] = "Terminé" ) ) VAR pctRealisationTermine = DIVIDE(nbrKidsTermine, nbrKidsTotal, 0 ) RETURN pctRealisationTermine

The only thing now is that if there are no completed ("Terminé") items, the measure returns empty instead of 0.

Re: Measure returning the correct value for one end of a relationship, and incorrect for the other end

nfuids — Thu, 04 Aug 2022 11:32:47 GMT

Found out COUNTROWS returns BLANK() if there are no ROWS. So I Added + 0 at the end of the COUNTROWS function and fixed my problem!

Re: Measure returning the correct value for one end of a relationship, and incorrect for the other end

daXtreme — Thu, 04 Aug 2022 13:44:34 GMT

Hi nfuids

I'd warn you against using many-to-many without a thorough understanding of what problem this feature is trying to solve unless you want to be in for a surprise; in fact, many surprises. Many-to-many should be used for solving granularity issues ONLY. Nothing else. Your problem clearly is not that. You should in all earnest create a bridge table in the usual manner and for one of the relationships enable the bidirectional filter. The bridge table should be hidden. This is Best Practice which you can find in Alberto's and Marco's works.

And yes, I've been through the Book several times... 🙂 Resolved millions of DAX problems, so speaking from experience.