Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper 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

aashton's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

Hello v-yueyunzh-msft !

So I did get the 2 measures to work, Applications Source and Contract Source. The drill-through is still kind of funny, sometimes it shows records from a different source than the one I clicked on...still trying to work that out.  But I have a second part of this I am trying to figure out.  See attached I've created two measures, for Application Count and Contract Count.  I need to display these in a matrix by source.  I've put these 2 new measures on Page 2 for the drill-throuh....However, when drill-through  on the matrix, it only brings the filter of the source not the tables.  So if I click on Agency Application Count of 5, it shows the 5 Application, plus any contracts with the source of Agency.  Is there a way to get it to filter on the table and source?

 

https://drive.google.com/file/d/1ZCASD5RnFeqP_lO-da_qdVS7q_zvi7tb/view?usp=sharing

 

 

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

Hi， aashton 

According to your description, when you drill through the table , it will not filter the [Table] field?

This is due to we put the measure as the value and we do not put the dimension on the row or columns.

So , if you want to filter the [Table] field in your table visual, the best way is add the [Table] field on the columns.

And then we create a measure like this:

Count = CALCULATE(DISTINCTCOUNT('Lead Application Contract Sources'[ID]), 'Lead Application Contract Sources'[Table]=MAX('Lead Application Contract Sources'[Table]))

Then we can drill through like the bar chart .

 

If i misunderstand your need , you can share me the end result in a situation as a table so that we can make this question more easiler.

 

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