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

aashton's avatar
aashton
Helper V
3 years ago

v-yueyunzh-msft  Thank you, yes that works.  But I have more measures i need to display in this matrix, so Table in the columns screws that up (the other measures aren't by table), for example:

 

 

 

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

Hi, aashton 

Thanks for your quick response! And according to your description, you have other measures need to put in this Matrix . And for drill through , it need the dimension so that we can push the filter to other page .

For your need , i think we need to create a dimension table as the column headers.

Here are the steps you can refer to :

 


(1)We can use the "Enter Data" to create a dimension table like this:

And this [Column1] is used to sort the [Type] field.

(2)Then we need to create a measure to return the according value :

Measure = var _type = MAX('Table'[Type])
var _name = MAX('Table'[Name])

return
IF(_type = "Applications" && _name = "Count" , [Application Count] , 
IF(_type = "Contracts" && _name = "Count" , [Contract Count] ,
IF( _name = "Other Measure" ,[Other Measure] ,
IF(_name = "Other Measure2" ,FORMAT([Other Measure 2],"0.00%")))))

 

Then we can put the fields on the visual :

 

(3)Then we need to create another measure like this:

Measure 2 = IF(  MAX('Lead Application Contract Sources'[Table]) in VALUES('Table'[Type]) ,1,0)

 

We can put this in the destination page visual on the "Filter on this visual":

 

(4)Then we can drill through from this table :

 

 

 

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
    Helper V
    3 years ago

    v-yueyunzh-msft This is great, thank you.  How is source related to the new table?  I am getting an error, can't determine the relationship:

     

     

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    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
    Helper V
    3 years ago

    v-yueyunzh-msft  Ohhhh OK, I understand.  I think I have it working...one more question...How did you get Other Measure and Measure 2 next to the Counts when drilling down the matrix?  When I drill-down, it only shows my extra measures and the total of count (not by table), and when I expand the entire hierarch, I get below:

    Drilled-down once:

     

     

    Expand hierarchy:

     

     

    My Measure:

    Measure Drill-Through = var _type = MAX('LAC Drill-Through'[Type])
    var _name = MAX('LAC Drill-Through'[Name])

    return
    IF(_type = "Lead" && _name = "Count", [Total Lead LAC],
    IF(_type = "Applications" && _name = "Count", [Total Application LAC],
    IF(_type = "Contracts" && _name = "Count", [Total Contracts LAC],
    IF(_name = "Lead to App Conversion", [Lead to App Conversion combo],
    IF(_name = "App to Contract Conversion", [App to Contract Conversion],
    IF(_name = "Lead to Contract Conversion", [Lead to App Conversion combo]))))))

     

     

     

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

    Hi, aashton 

    Thanks for your quick response ! Accoridng to your description, when you drill  down the table visual , it dose not display the first hirearchity . 

    For this , we need to click the "Expand all down one level in the hierarchy" so that we can keep the first hirearchity show in this visual:

    And you can also see this dimension table we have created  and it corresponse each level of relationship.We can understand by this image :

     

    And we can also see the drill down differnce in this document , you can kindly refer to:

    Drill mode in the Power BI service - Power BI | Microsoft Learn

     

     

    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