Forum Discussion

aashton's avatar
aashton
Helper V
3 years ago
Solved

Table set-up - Union into one table

Hello, I have an Applications table which includes Date, ID, Email, Name, Source etc.  I also have a Contracts table which shows similar fields, Date, ID, Email, Name and Source.  All the the visual...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , aashton 

    According to your screenshot,I see you have created the dimension table.For this table is used for the columns headers . And you put the right place,the row header is used by the fact table.
    For your need ,we do not need to create relationship between the dimension table and the fact table.
    Instead we need use the measure to get the value without relationships between tables.You do not worry about you get an error code when you put this three fields on the visual,we just need to create a measure to put on the value field so the error code will disappear!
    And the dimension table is created by which fields you need to put on the column headers,in my test data ,they are “application count,contracts count,other measure ,other measure 2”.You need to modify this dimension table in your side based on your situation.
    And then you can see the [Measure],this is to get the value based on the column headers,you also need to modify this measure based on your situation!
    The [Measure2] is used for the drill through due to now when we drill through we convert the [Type] field instead of the [Table],so we need to create a new measure to transfer this filter to what we want !

     

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi， aashton 

 According to your description,  you have two tables with the same structure .And you have unioned them to one table .Then you create two measures to as the Y-axis and when you drill through the visual , the value can not be filtered.

For your need , i see the dax code you create is used the differnt field to get the count .

 

I think that if you have two tables with the same field, then you don’t need to separate two fields to calculate the count, you can directly put them in the same column to evaluate, and you don’t need to create a measure,like this table:

And then we can put the field on the visual like this:

 

And then we can configure the drill-through page like this:

 

Then we can drill through from page 1 to page 2. 
Because the data I guess may be different from your actual data, if this can't help you solve the problem, can you provide me with a sample .pbix (without sensitive data) for testing(You can upload the sample .pbix file to OneDrice and share the link to me )? This can help you solve the problem faster.
 
 

 

aashton's avatar
aashton
Helper V
3 years ago

v-yueyunzh-msft  Yes, thank you, that works.  It looks like I was overthinking it.  But, how/could this work in a matrix?  I have a matrix like below with multiple measures along the top (Lead, Application and Contracts are the values for field table):