COUNTROWS DISTINCT UNION VALUES vs merge plus conditional column

[Doranov]

Apologies for the weird subject. I don't speak PowerBI well enough to come up with a proper title.

While trying to adhere to the star scheme, I ran into the following issue.

A student has an admission, and this admission can gain the "admissible" and "registered" status. In theory, every admission with the "registered" status should also be "admissible" (admission -> admissible -> registration). But real life disagrees (missing data), so I have to correct the "admissible" status with knowledge from the registrations data. Admissions, Admissible, and Registrations are separate tables. Dropbox links are provided below. Note that these have to be downloaded as Dropbox prohibits viewing a .pbix file.

Previously I would solve this issue by merging (left outer join) both "Admissible" and "Registered" to "Admissions". I would then create columns "Admissible?", "Registered?", and "Admissible (corr.)?". The latter being a conditional column that checks whether "Registered?" is 1, and otherwise it grabs the value from "Admissible?". Pretty easy, but it's a flat table.

In my star scheme I came up with the following formula:

Total admissible (corr.) = COUNTROWS(DISTINCT(UNION(VALUES(Registrations[Student Number]),VALUES(Admissible[Student Number]))))

It seems to work, and it kind of makes sense (I worked outwards when building this formula). But I doubt this would fall into the best practice category. How would an experienced PowerBI developer solve this?

Alternatively I could remove the registration, instead of adding to admissible. Probably I would then need to use INTERSECT instead of UNION?

Downloads:

https://www.dropbox.com/s/stl6yciasirnuap/if%20Registered%20then%20Admissible.png?dl=0 (screenshot of data model)

https://www.dropbox.com/s/ecurktlw7n7eak5/if%20Registered%20then%20Admissible.pbix?dl=0 (pbix file)
https://www.dropbox.com/s/rmrflnn2kcmezyo/if%20Registered%20then%20Admissible.xlsx?dl=0 (excel data)

[v-yangliu-msft]

Hi  @Doranov ,

I am not very clear about your needs.... Can you use pictures to describe the results you need to get, we can better help you.

Best Regards,

Liu Yang

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

[Doranov]

Finally figured out how to include an image. It's insert photo instead of insert media.

Below you can see the data model. There are three fact tables: Admissible, Admissions, and Registrations.

The data for these tables comes from different sources.

Admissible looks at historical data. A student is admissible if the admission category has ever been "conditionally approved", or "approved". Once a student is admissible, that student can get a registration. This implies that every student with a registration must have been admissible. However, because of issues with my source data, not all registered students can be found in the Admissible table. I want to correct this.

I could easily solve this by merging Admissible with Registrations, and adding a conditional column "if registered then admissible". But I want to avoid merging tables, because that eventually just leads to creating one big table. I tried to come up with a measure that could count the number of admissible students based on information from both the Admissible and the Registrations tables.

There are probably other solutions to this as well. It seems like a pretty common scenario. I'm wondering how experienced PowerBI developers handle this.