Create a virtual table with a measure and distinctcount

[Anonymous]

Hi all.

Can someone please help me solving this problem with me? I would really appreciate it.

So;

I have one table who looks like this:

|ID| |Coverage_x| |Coverage_y|

The ID here does not contain distinct values.

Then I have two fact tables connected to the first table.

The first of this contains:

|Coverage_x| |more info|

The other contains:

|Coverage_y| |more info|

This two tables are connected to the first on |Coverage_x| and |Coverage_y|

What i now want is to count distinct values in the first table with filter from the two others (Coverage_x and Coverage_y)

If this two tables was in one table i could use:
measure = CALCULATE(DISTINCTCOUNT(Table1[ID]); (Table2[Coverage_x])) || (Table2[Coverage_y])
But when they are in two seperate tables this does not work.

Then I have to make a new virtual table that contains info from all three tables and then DISTINCTCOUNT |ID| with the filters I want.

I cannot merge the two tables into the first, because the two other tables contains a lot of columns but a few rows.

Hope this was understandable, but please let me know if there is anything more you need to solve this.

Best regards

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please check if the following measure returns your expected result. If not, please post sample data as other's post.

Measure = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(Table1,Table1[Coverage_x]=RELATED(Table2[Coverage_x])||Table1[Coverage_y]=RELATED(Table3[Coverage_y])))


Regards,
Lydia

[Anonymous]

Thank you so much.

This worked exactly how I wanted 🙂

Marius

[Greg_Deckler]

Sample data that can be easily copied and pasted would help recreate but my first suggestion would be to throw a RELATED or RELATEDTABLE into your CALCULATE for your Coverage_x and Coverage_y.