Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Create a virtual table with a measure and distinctcount

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

 

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

      Thank you so much.


      This worked exactly how I wanted :)

      Marius

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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.