Finding a Median with relationships and missing data

[Coriel-11]

Hi everyone,

I have two tables. And "Events" table which looks a bit like this

"Webinar ID", "Title",
"999888777", "Getting good at Power BI"

"999888778", "Getting very good at Power BI"

and an "Attendees" Table which looks like

"RegID", "Web ID", "Time attended"

"55556", "999888777", "45"

"55557", "999888777", "10"

"55558", "999888777", ""

I want to calculate the median people per event, BUT, not all of the events listed in the Attendees Table have actually been added to the Events table, which seems to be skewing the figures.

Now I could join these in Power Query to filter them, but that seems like it would slow things down. Or tidy up the original dtaa sources, but that's going to work to make them dynamic. I'd hoped to be able to manage the relationsihp between the tables so it only included "inner" joins, but it seems I can't do that. So I'm trying to do it via DAX, but struggling to wrap my brain around it.

My initial median code was this:

Median regs =

var tabley = SUMMARIZE('Attendees',Attendees[EventID],"CountofRegID",COUNTA(Attendees[RegID]))

Return

MEDIANX(tabley,[CountofRegID])

Anyone got any ideas?

[Coriel-11 · Accepted solution]

Hi @v-shex-msft,

Thanks for your message. I am familiar with that post and thought I had provided enough data to give people an idea, but I guess obviously not! 

I actually figured it out in the end, albeit not as neatly as I'd hoped.

Median regs =
var tablx = CALCULATETABLE('Attendees',INTERSECT(VALUES(Attendees[Web ID]),VALUES(Events[Webinar ID])))
var tably = SUMMARIZE(tablx,Attendees[Web ID],"CountofRegID",COUNTA(Attendees[RegID]))
Return
MEDIANX(tably,[CountofRegID])

I'd still be interested to know if there is a simpler way to do this, but reasonably happy with the above.
Thanks.

Matt

[v-shex-msft]

Hi @Coriel-11,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng